Making a Delete Qiuery from an unmatched Query

[DZ]

I want to delete records from a table based on an unmatched Query.

I created an unmatched Query and it returns the records i want deleted.

I went to the Query menu in Design view 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?

Here is the SQL for the unmatched query,

SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.[Registered Under What Address] =
Table1.[Registered Under What Address]) AND (Table2.[Registered Under What
Name] = Table1.[Registered Under What Name]) AND (Table2.Location =
Table1.Location)
WHERE (((Table1.Location) Is Null) AND ((Table1.[Registered Under What
Name]) Is Null) AND ((Table1.[Registered Under What Address]) Is Null));

Can anyone help me by rewriting this SQL to make it a Delete query that will
delete the records returned by this unmatched Query

 

[DZ]

Bonnie

John Spencer posted this. I think it is correct !

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

 

[Michel Walsh]

You can also use DISTINCTROW (a keyword) right after the keyword SELECT and
the query will become updateable, in this case. You can 'add' that keyword
through the graphical editor, without switching in SQL view, if you have the
Query Property sheet open: Change the Unique Records property to Yes.


Vanderghast, Access MVP

Looks like you can do it but not in the query design view - use SQL view.

I knew one of the John's would do it.

Thanks!

Bonnie

[quoted text clipped - 6 lines]

and Table1.Address = Table2.Address
and Table1.Location = Table2.Location)

 

[DZ]

Michael

Thanks

Yes This works equally well

Great !