Creating a Delete Query

[mistereli]

In Access2000 I would like to create a delete query using as criteria a table of selected records to delete from two other tables.

Table #1 lists only the specific serial numbers that I would like use as the criteria. And the other two tables are Inventory and History. All have the serial number in common. The Inventory table has 200 records but the History has about 2000 (because there are multiple records for each serial number).

I've tried to create the delete query but with no success. I am limited in my knowledge of creating queries and would greatly appreciate anyones help. Thanks!

 

[lespaul]

DELETE FROM INVENTORY WHERE SERIALNUM IN (SELECT SERIALNUM FROM TABLE#1)

DELETE FROM HISTORY WHERE SERIALNUM IN (SELECT SERIALNUM FROM TABLE#1)

It may be possible to do this in one query, but these should do what you want. Be sure to change the table and field names and just copy into the SQL view.

HTH

Leslie

 

[Golom]

I'm not clear if you want to delete records from History (for example) when the serial is NOT in Table1 OR if you want to delete where serial IS in Table1. Assuming the first option

Delete * From History
Where Serial NOT IN (Select Serial From Table1)

 

[mistereli]

Thank you!