Deleting Records

[JLong]

I have to tables with identical structure(scheme), they
where supposed to contain defferent records. However, the
second table was created by copying the first one without
deleting its contents. I can select the defference and
make it a delete query, but when I try to run it, I get a
message "Specify the table containing the records you want
to delete". I am using Access 2002, here is the SQL query

DELETE Table2.Name, Table2.Phone
FROM Table1 RIGHT JOIN Table2 ON Table1.Name = Table2.Name
WHERE (((Table1.Name) Is Null));

Can someone help me? Thanks in advance.

 

[John Vinson]

I can select the defference and
make it a delete query, but when I try to run it, I get a
message "Specify the table containing the records you want
to delete".

Change the SQL to

DELETE Table2.*
FROM Table1 RIGHT JOIN Table2 ON Table1.Name = Table2.Name
WHERE (((Table1.Name) Is Null));


You *might* need to turn the join around:

DELETE Table2.*
FROM Table2 LEFT JOIN Table1 ON Table2.Name = Table1.Name
WHERE (((Table1.Name) Is Null));

For future reference - do note that NAME is a reserved word, and
therefore a bad choice for a field name; and that people's names are
NOT unique. If the NAME field is the name of a person in your
database, you might end up deleting Fred Smith (from Poughkeepsie)
when you intended to delete Fred Smith (the one from Weehawken).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JLong]

John thank you for your reply. I used those two table
just to illustrate my problem. I tryied your suggestion
and still can't run the query, I still get the same
message asking to specify the table containing the records
to be deleted. I seems that it doesn't matter whether i
create the query on the SQL View or on the designer, even
using the Find Unmatched Query Wizard I get the same
message. It works as far as selecting the difference, but
can't delete them. Any other suggestions? Access 2002.

 

[John Vinson]

John thank you for your reply. I used those two table
just to illustrate my problem. I tryied your suggestion
and still can't run the query, I still get the same
message asking to specify the table containing the records
to be deleted. I seems that it doesn't matter whether i
create the query on the SQL View or on the designer, even
using the Find Unmatched Query Wizard I get the same
message. It works as far as selecting the difference, but
can't delete them. Any other suggestions? Access 2002.

The key is that the DELETE clause should contain *only* the * field
from the table from which you wish to delete records. If you have
other fields selected in the query grid (say for criteria) you can
uncheck the Show checkbox. Or, post the actual SQL of your query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps