Delete sub query problems

  • Thread starter Thread starter Opal
  • Start date Start date
O

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!
 
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));
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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.
 
Back
Top