Delete Query

[Guest]

I've created a delete query using a left join on two tables that identifies
the data rows I want to delete. However, I get an error message that tells
me too "specify the table containing the records I want to delete"? How do I
correct this error?

 

[Steve Schapel]

MC,

Can you please post back with some more setails, preferably the SQL view
of the query you're trying with so far? Thanks.

 

[Guest]

Below is the sql statement. Thanks.

DELETE ALMOST.Co, ALMOST.ST, ALMOST.[Opr #], ALMOST.[Well No],
ALMOST.Operator, ALMOST.[Well Name], ALMOST.[Prod Date], ALMOST.[Current Gas
Imb], ALMOST.[Prior Mo Gas IMb], ALMOST.Status, ALMOST.[Stmt Mailed],
ALMOST.[DOM requests], ALMOST.PIPELINE, ALMOST.Purchaser, ALMOST.SYSTEM,
ALMOST.GWI, ALMOST.[Last Request], ALMOST.Comments, ALMOST.[P&A/Sell Form],
ALMOST.[P&A/Sell Form to Acctg], ALMOST.GBA, ALMOST.[MAKE-UP], ALMOST.[TERMS
FOR GBA], ALMOST.County, ALMOST.[LEGAL DESCRIPTION], ALMOST.ACQ, ALMOST.[SOLD
DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves, ALMOST.ID
FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No];

 

[Steve Schapel]

MC,

A Delete Query needs to have the table specified. The SQL always looks
like this...
DELETE TableName.* FROM ...

It is not clear what you are trying to achieve with the Left Join. This
would appear to indicate that you have records in the 2_073105 table
which do not have corresponding records in the Almost table. Is that
right? And in that case, the data returned by the query will include
these records, right? So how do the non-existent Almost records relate
to what you are trying to achieve, if you catch my drift here? Are you
really trying to delete all records from the Almost table where there
*are* matching records in the 2_073105 table? If so, the SQL would be
like this...
DELETE ALMOST.*
FROM ALMOST INNER JOIN 2_073105
ON ALMOST.[Well No]=[2_073105].QEP

Otherwise, if I have misunderstood, please try to explain what you want
to happen.

 

[Guest]

Thank you - your reponse was right on -- however, I am getting an additional
error that says "Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could not
be completed." I've checked the permissions?

--
thanks, mc

MC,

A Delete Query needs to have the table specified. The SQL always looks
like this...
DELETE TableName.* FROM ...

It is not clear what you are trying to achieve with the Left Join. This
would appear to indicate that you have records in the 2_073105 table
which do not have corresponding records in the Almost table. Is that
right? And in that case, the data returned by the query will include
these records, right? So how do the non-existent Almost records relate
to what you are trying to achieve, if you catch my drift here? Are you
really trying to delete all records from the Almost table where there
*are* matching records in the 2_073105 table? If so, the SQL would be
like this...
DELETE ALMOST.*
FROM ALMOST INNER JOIN 2_073105
ON ALMOST.[Well No]=[2_073105].QEP

Otherwise, if I have misunderstood, please try to explain what you want
to happen.

--
Steve Schapel, Microsoft Access MVP

Below is the sql statement. Thanks.

DELETE ALMOST.Co, ALMOST.ST, ALMOST.[Opr #], ALMOST.[Well No],
ALMOST.Operator, ALMOST.[Well Name], ALMOST.[Prod Date], ALMOST.[Current Gas
Imb], ALMOST.[Prior Mo Gas IMb], ALMOST.Status, ALMOST.[Stmt Mailed],
ALMOST.[DOM requests], ALMOST.PIPELINE, ALMOST.Purchaser, ALMOST.SYSTEM,
ALMOST.GWI, ALMOST.[Last Request], ALMOST.Comments, ALMOST.[P&A/Sell Form],
ALMOST.[P&A/Sell Form to Acctg], ALMOST.GBA, ALMOST.[MAKE-UP], ALMOST.[TERMS
FOR GBA], ALMOST.County, ALMOST.[LEGAL DESCRIPTION], ALMOST.ACQ, ALMOST.[SOLD
DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves, ALMOST.ID
FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No];

 

[SusanV]

Is there something running prior to this that is locking the table? You
could try closing the table via code first...

Just a thought,

Susan

Thank you - your reponse was right on -- however, I am getting an
additional
error that says "Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could
not
be completed." I've checked the permissions?

--
thanks, mc

MC,

A Delete Query needs to have the table specified. The SQL always looks
like this...
DELETE TableName.* FROM ...

It is not clear what you are trying to achieve with the Left Join. This
would appear to indicate that you have records in the 2_073105 table
which do not have corresponding records in the Almost table. Is that
right? And in that case, the data returned by the query will include
these records, right? So how do the non-existent Almost records relate
to what you are trying to achieve, if you catch my drift here? Are you
really trying to delete all records from the Almost table where there
*are* matching records in the 2_073105 table? If so, the SQL would be
like this...
DELETE ALMOST.*
FROM ALMOST INNER JOIN 2_073105
ON ALMOST.[Well No]=[2_073105].QEP

Otherwise, if I have misunderstood, please try to explain what you want
to happen.

--
Steve Schapel, Microsoft Access MVP

Below is the sql statement. Thanks.

DELETE ALMOST.Co, ALMOST.ST, ALMOST.[Opr #], ALMOST.[Well No],
ALMOST.Operator, ALMOST.[Well Name], ALMOST.[Prod Date],
ALMOST.[Current Gas
Imb], ALMOST.[Prior Mo Gas IMb], ALMOST.Status, ALMOST.[Stmt Mailed],
ALMOST.[DOM requests], ALMOST.PIPELINE, ALMOST.Purchaser,
ALMOST.SYSTEM,
ALMOST.GWI, ALMOST.[Last Request], ALMOST.Comments, ALMOST.[P&A/Sell
Form],
ALMOST.[P&A/Sell Form to Acctg], ALMOST.GBA, ALMOST.[MAKE-UP],
ALMOST.[TERMS
FOR GBA], ALMOST.County, ALMOST.[LEGAL DESCRIPTION], ALMOST.ACQ,
ALMOST.[SOLD
DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves,
ALMOST.ID
FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No];

 

[Guest]

Thanks for the feedback, I have learned quite abit with the information
intereacted on this posting. I have since approached the problem from a
different angle and have had success. I would like to know if a website
exists that has examples of delete queries using multiple tables and joins?
Thanks again.
--

Is there something running prior to this that is locking the table? You
could try closing the table via code first...

Just a thought,

Susan

Thank you - your reponse was right on -- however, I am getting an
additional
error that says "Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could
not
be completed." I've checked the permissions?

--
thanks, mc

MC,

A Delete Query needs to have the table specified. The SQL always looks
like this...
DELETE TableName.* FROM ...

It is not clear what you are trying to achieve with the Left Join. This
would appear to indicate that you have records in the 2_073105 table
which do not have corresponding records in the Almost table. Is that
right? And in that case, the data returned by the query will include
these records, right? So how do the non-existent Almost records relate
to what you are trying to achieve, if you catch my drift here? Are you
really trying to delete all records from the Almost table where there
*are* matching records in the 2_073105 table? If so, the SQL would be
like this...
DELETE ALMOST.*
FROM ALMOST INNER JOIN 2_073105
ON ALMOST.[Well No]=[2_073105].QEP

Otherwise, if I have misunderstood, please try to explain what you want
to happen.

--
Steve Schapel, Microsoft Access MVP


dallin wrote:
Below is the sql statement. Thanks.

DELETE ALMOST.Co, ALMOST.ST, ALMOST.[Opr #], ALMOST.[Well No],
ALMOST.Operator, ALMOST.[Well Name], ALMOST.[Prod Date],
ALMOST.[Current Gas
Imb], ALMOST.[Prior Mo Gas IMb], ALMOST.Status, ALMOST.[Stmt Mailed],
ALMOST.[DOM requests], ALMOST.PIPELINE, ALMOST.Purchaser,
ALMOST.SYSTEM,
ALMOST.GWI, ALMOST.[Last Request], ALMOST.Comments, ALMOST.[P&A/Sell
Form],
ALMOST.[P&A/Sell Form to Acctg], ALMOST.GBA, ALMOST.[MAKE-UP],
ALMOST.[TERMS
FOR GBA], ALMOST.County, ALMOST.[LEGAL DESCRIPTION], ALMOST.ACQ,
ALMOST.[SOLD
DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves,
ALMOST.ID
FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No];

 

[SusanV]

I've have these bookmarked for ages - nice reference:

Fundamental Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Intermediate Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Advanced Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp


Also, http://www.techonthenet.com/ has lots of excellent stuff, SQL, VBA
syntax etc.

--
hth,
SusanV

Thanks for the feedback, I have learned quite abit with the information
intereacted on this posting. I have since approached the problem from a
different angle and have had success. I would like to know if a website
exists that has examples of delete queries using multiple tables and
joins?
Thanks again.
--

Is there something running prior to this that is locking the table? You
could try closing the table via code first...

Just a thought,

Susan

Thank you - your reponse was right on -- however, I am getting an
additional
error that says "Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion
could
not
be completed." I've checked the permissions?

--
thanks, mc


:

MC,

A Delete Query needs to have the table specified. The SQL always
looks
like this...
DELETE TableName.* FROM ...

It is not clear what you are trying to achieve with the Left Join.
This
would appear to indicate that you have records in the 2_073105 table
which do not have corresponding records in the Almost table. Is that
right? And in that case, the data returned by the query will include
these records, right? So how do the non-existent Almost records
relate
to what you are trying to achieve, if you catch my drift here? Are
you
really trying to delete all records from the Almost table where there
*are* matching records in the 2_073105 table? If so, the SQL would be
like this...
DELETE ALMOST.*
FROM ALMOST INNER JOIN 2_073105
ON ALMOST.[Well No]=[2_073105].QEP

Otherwise, if I have misunderstood, please try to explain what you
want
to happen.

--
Steve Schapel, Microsoft Access MVP


dallin wrote:
Below is the sql statement. Thanks.

DELETE ALMOST.Co, ALMOST.ST, ALMOST.[Opr #], ALMOST.[Well No],
ALMOST.Operator, ALMOST.[Well Name], ALMOST.[Prod Date],
ALMOST.[Current Gas
Imb], ALMOST.[Prior Mo Gas IMb], ALMOST.Status, ALMOST.[Stmt
Mailed],
ALMOST.[DOM requests], ALMOST.PIPELINE, ALMOST.Purchaser,
ALMOST.SYSTEM,
ALMOST.GWI, ALMOST.[Last Request], ALMOST.Comments, ALMOST.[P&A/Sell
Form],
ALMOST.[P&A/Sell Form to Acctg], ALMOST.GBA, ALMOST.[MAKE-UP],
ALMOST.[TERMS
FOR GBA], ALMOST.County, ALMOST.[LEGAL DESCRIPTION], ALMOST.ACQ,
ALMOST.[SOLD
DATE], ALMOST.[SOLD TO], ALMOST.[FILE LOCATION], ALMOST.Reserves,
ALMOST.ID
FROM 2_073105 LEFT JOIN ALMOST ON [2_073105].QEP = ALMOST.[Well No];

 

[Steve Schapel]

MC,

I would be interested to hear what is involved with your solution.

There is a topic in Access Help about "when can I update data in a
query". There are a number of factors that determine whether a query is
"updateable" or not. One of the considerations for a Delete Query to
work is that the data set returned by the query must be updateable.

 

[Guest]

I reversed my approach and on the 1000 record table, instead of going after
the 20 records I wanted to delete and reappend, I created a new table for the
980 records I didn't want to delete and then appended the new records to the
new table - deleted all records in the main 1000 record table and finally
appended the new table records back to the orig/main table. I recognize its
not the most efficient approach but for now it did the job. I plan on
learning from the web sites posted.