Delete sub query problems

[Opal]

I am trying to create a subquery to delete unmatched records between
two
tables in Access 2003. I patterned by subquery from Allen Browne's
website as follows:

DELETE FROM EmpInfo
WHERE NOT EXISTS
(SELECT EmpNumber
FROM EmpInfoTemp
WHERE EmpInfoTemp.EmpNumber = EmpInfo.EmpNumber);

But am getting the following message:
"Query must have at least one destination field"

I am using a subquery because when I tried to create the query using
the
unmatched query wizard I get an error:
"Could not delete from specified tables"

Also, I want to add two additonal criteria to the query, i.e.:

WHERE ((EmpInfo.SupLink)<>"QRA0" And (EmpInfo.SupLink)<>"QRB0")

Can someone please point me in the right direction to get this to
work?
Thank you!

 

[Allen Browne]

That should work.

Make sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html
Then compact/repair.

Make sure there is nothing in your query's Filter or OrderBy properties, or
those of either table.

It might not hurt to be explicit about the table name in the subquery.

Presumably EmpNumber is the same data type in the 2 tables.

So:

DELETE FROM EmpInfo
WHERE NOT (EmpInfo.SupLink IN ("QRA0", "QRA0")
OR EXISTS
(SELECT EmpInfoTemp.EmpNumber
FROM EmpInfoTemp
WHERE EmpInfoTemp.EmpNumber = EmpInfo.EmpNumber));

 

[Opal]

That should work.

Make sure Name AutoCorrect is off:
   http://allenbrowne.com/bug-03.html
Then compact/repair.

Make sure there is nothing in your query's Filter or OrderBy properties, or
those of either table.

It might not hurt to be explicit about the table name in the subquery.

Presumably EmpNumber is the same data type in the 2 tables.

So:

DELETE FROM EmpInfo
WHERE NOT (EmpInfo.SupLink IN ("QRA0", "QRA0")
OR EXISTS
    (SELECT EmpInfoTemp.EmpNumber
     FROM EmpInfoTemp
     WHERE EmpInfoTemp.EmpNumber = EmpInfo.EmpNumber));

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.












- Show quoted text -

Curiouser and curiouser....same error.... for some reason
after I edit the SQL statement and then switch to design view
it drops the EmpInfoTemp table and I get the same error message:

"Query must have at least one destination field"

which I presume might have something to do with your statement:
"It might not hurt to be explicit about the table name in the
subquery."

Sorry for my ignorance, but how would I do that?

BTW, AutoCorrect is off, I performed a compact and repair, no filter
or order by properties in either query or table and both EmpNumber
in both tables are "Number" Data Types.

 

[Allen Browne]

I didn't follow this bit:
for some reason after I edit the SQL statement
and then switch to design view
it drops the EmpInfoTemp table

The subquery table does not show in query design view, so if it shows only
the EmpInfo table in the upper pane of query design, that is correct.

In the example I posted, the SELECT clause in the subquery:
(SELECT EmpInfoTemp.EmpNumber
includes the table name. That's what I meant by explicitly naming the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That should work.

Make sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html
Then compact/repair.

Make sure there is nothing in your query's Filter or OrderBy properties,
or
those of either table.

It might not hurt to be explicit about the table name in the subquery.

Presumably EmpNumber is the same data type in the 2 tables.

So:

DELETE FROM EmpInfo
WHERE NOT (EmpInfo.SupLink IN ("QRA0", "QRA0")
OR EXISTS
(SELECT EmpInfoTemp.EmpNumber
FROM EmpInfoTemp
WHERE EmpInfoTemp.EmpNumber = EmpInfo.EmpNumber));

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.












- Show quoted text -

Curiouser and curiouser....same error.... for some reason
after I edit the SQL statement and then switch to design view
it drops the EmpInfoTemp table and I get the same error message:

"Query must have at least one destination field"

which I presume might have something to do with your statement:
"It might not hurt to be explicit about the table name in the
subquery."

Sorry for my ignorance, but how would I do that?

BTW, AutoCorrect is off, I performed a compact and repair, no filter
or order by properties in either query or table and both EmpNumber
in both tables are "Number" Data Types.

 

[Allen Browne]

I didn't follow this bit:
for some reason after I edit the SQL statement
and then switch to design view
it drops the EmpInfoTemp table

The subquery table does not show in query design view, so if it shows only
the EmpInfo table in the upper pane of query design, that is correct.

In the example I posted, the SELECT clause in the subquery:
(SELECT EmpInfoTemp.EmpNumber
includes the table name. That's what I meant by explicitly naming the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That should work.

Make sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html
Then compact/repair.

Make sure there is nothing in your query's Filter or OrderBy properties,
or
those of either table.

It might not hurt to be explicit about the table name in the subquery.

Presumably EmpNumber is the same data type in the 2 tables.

So:

DELETE FROM EmpInfo
WHERE NOT (EmpInfo.SupLink IN ("QRA0", "QRA0")
OR EXISTS
(SELECT EmpInfoTemp.EmpNumber
FROM EmpInfoTemp
WHERE EmpInfoTemp.EmpNumber = EmpInfo.EmpNumber));

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.












- Show quoted text -

Curiouser and curiouser....same error.... for some reason
after I edit the SQL statement and then switch to design view
it drops the EmpInfoTemp table and I get the same error message:

"Query must have at least one destination field"

which I presume might have something to do with your statement:
"It might not hurt to be explicit about the table name in the
subquery."

Sorry for my ignorance, but how would I do that?

BTW, AutoCorrect is off, I performed a compact and repair, no filter
or order by properties in either query or table and both EmpNumber
in both tables are "Number" Data Types.

 

[MGFoster]

I am trying to create a subquery to delete unmatched records between
two
tables in Access 2003. I patterned by subquery from Allen Browne's
website as follows:

DELETE FROM EmpInfo
WHERE NOT EXISTS
(SELECT EmpNumber
FROM EmpInfoTemp
WHERE EmpInfoTemp.EmpNumber = EmpInfo.EmpNumber);

But am getting the following message:
"Query must have at least one destination field"

< SNIP >

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually in NOT EXISTS sub-queries the SELECT clause only has * instead
of a column list:

WHERE NOT EXISTS
(SELECT *
FROM EmpInfoTemp
... etc. ...

Perhaps this is the problem.

You might also want to try this solution (untested):

DELETE EmpInfo.*
FROM EmpInfo LEFT JOIN EmpInfoTemp ON EmpInfo.EmpNumber =
EmpInfoTemp.EmpNumber
WHERE EmpInfoTemp.EmpNumber IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **



-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbBwkYechKqOuFEgEQLNIQCgrTcqrSOAKi+YgUBjEdW57r9NK7IAnjyl
saY27a6ryS1axdLV/U9eQPsd
=IMvU
-----END PGP SIGNATURE-----