Delete Query - with Criteria from SubQuery

[DZ]

I have two tables. Table1 and Table2.
Both have 3 fields in common: Name, Address and Location fields.

Table1 has duplicate values of Name, Address and Location combined.
Table2 has unique values of Name, Address and Location combined. In table2
the combined fields: Name, Address and Location are the primary key.

I want to delete all records from table2 where they have no corresponding
combined value of Name, Address and Location in table1.

Table2 has important information in the other fields in records where Name,
Address and Location combination already exists in Table1, so I don't want to
those record deleted... but all other records I want deleted from table2.

Thanks for any help with this

 

[pietlinden]

I have two tables. Table1 and Table2.
Both have 3 fields in common: Name, Address and Location fields.

Table1 has duplicate values of Name, Address and Location combined.
Table2 has unique values of Name, Address and Location combined. In table2
the combined fields: Name, Address and Location are the primary key.

I want to delete all records from table2 where they have no corresponding
combined value of Name, Address and Location in table1.

Table2 has important information in the other fields in records where Name,
Address and Location combination already exists in Table1, so I don't want to
those record deleted... but all other records I want deleted from table2.

Thanks for any help with this

sounds like a find unmatched query...
SELECT A.Field1,...
FROM A LEFT JOIN B ON A.PK=B.FK
WHERE B.FK IS NULL

And then you turn that into a delete query or use IN...

 

[DZ]

I created an unmatched Query and it's returning the records I want to delete,
In Query design view I went to the Query menu and selected Delete query to
turn it into a Delete query. When I run the query I get a message that says

"Specify the table containing the record you want to delete"

How do I specify the table to delete?

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

DELETE FROM Table2
WHERE NOT Exists
(SELECT * FROM Table1
WHERE Table1.Name = Table2.Name
and Table1.Address = Table2.Address
and Table1.Location = Table2.Location)

That can be slow with a large amount of data. If it is too slow, post back
for an alternative solution.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[DZ]

That's it

You are the man !

Regards