delete records in 1 table based on another

[Guest]

I have a table with 2,000 records, each with a unique account number.

I have another table with 120 records, each also with a unique account
number. The 120 records may also be included in the larger table.

If they are, I want to delete them from the larger table.

So my query would be, "Delete all the records in the bigger table that have
an account number contained in the smaller table."

What's the code for that?

Thanks.

Jerry

 

[Allen Browne]

1. Create a new query.

2. Cancel the Add Table dialog.

3. Switch to SQL View (View menu.)

4. Paste this in:
DELETE FROM [Table1]
WHERE EXISTS
(SELECT [Account] FROM [Table2]
WHERE [Table2].[Account] = [Table1].[Account]);

5. Adjust the table and field names.
Table1 represents the table that will lose the records.

The Delete query uses a subquery to identify the records that have a match.
If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066