Delete unmatched ID#s

  • Thread starter Thread starter Ellen
  • Start date Start date
E

Ellen

Hello,
I have two tables, table1 and table2. Both have an ID#
field. I'd like to remove records from table1 where there
are no matching ID#s in table2.

Thanks in advance,
Ellen
 
Ellen

Starting from the database window, select one of the two tables. Click on
the (drop-down) New Object toolbar button and select Query.

Select the "... Unmatched..." query wizard and follow prompts. You should
end up with a query that lists all rows of table 1 than are not in table 2,
based on your matching criteria. You should be able to then delete those.

NOTE! Backup your .mdb before the Delete!
 
Thank, Jeff. I have been able to do the unmatched query
as you described, but when I modify this query to a delete
query, it asks me to "Specify the table containing the
records you want to delete".
 
I got it.
DELETE [Table1].*, Table2.[ID#]
FROM [Table1] LEFT JOIN Table2 ON [Table1].[ID#] = Table2.
[ID#]
WHERE (((Table2.[ID#]) Is Null));

or something like that. My exact query is:
DELETE [Petra's MOLTable].*, CEDI.[ID#]
FROM [Petra's MOLTable] LEFT JOIN CEDI ON [Petra's
MOLTable].[ID#] = CEDI.[ID#]
WHERE (((CEDI.[ID#]) Is Null));
 
Pardon me for jumping in.

Which of the two tables do you want to delete records from? Whichever it is;
leave that table in the delete clause and get rid of the other in the DELETE
clause. To delete the records from Petra's MOLTable:

DELETE [Petra's MOLTable].*
FROM [Petra's MOLTable] LEFT JOIN CEDI ON
[Petra's MOLTable].[ID#] = CEDI.[ID#]
WHERE (((CEDI.[ID#]) Is Null));


To delete records from CEDI:

DELETE CEDI.[ID#]
FROM [Petra's MOLTable] LEFT JOIN CEDI ON
[Petra's MOLTable].[ID#] = CEDI.[ID#]
WHERE (((CEDI.[ID#]) Is Null));


I got it.
DELETE [Table1].*, Table2.[ID#]
FROM [Table1] LEFT JOIN Table2 ON [Table1].[ID#] = Table2.
[ID#]
WHERE (((Table2.[ID#]) Is Null));

or something like that. My exact query is:
DELETE [Petra's MOLTable].*, CEDI.[ID#]
FROM [Petra's MOLTable] LEFT JOIN CEDI ON [Petra's
MOLTable].[ID#] = CEDI.[ID#]
WHERE (((CEDI.[ID#]) Is Null));
-----Original Message-----
Thank, Jeff. I have been able to do the unmatched query
as you described, but when I modify this query to a delete
query, it asks me to "Specify the table containing the
records you want to delete".
.
Click to expand...
Click to expand...
 
Back
Top