A query that shows "deletable" records

[Lasse T]

Hello
Can anyone tell me how to find records in a table that has no related
records in two other tables?

I want to find the records that I can delete without having to just try
record after record.

It woud be nice if I also could delete records from that query.

Thanks in advance.

Lasse T
------------

 

[Jeff Boyce]

Lasse

I believe the Query Wizard includes a "not in" query. It sounds like you
are trying to find records in Table1 that are "not it" Table2.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hello
Can anyone tell me how to find records in a table that has no related
records in two other tables?

I want to find the records that I can delete without having to just try
record after record.

It woud be nice if I also could delete records from that query.

Thanks in advance.

Lasse T

Well, back up your database first just in case this blows up but...

SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.keyfield = Table2.keyfield
WHERE Table2.keyfield IS NULL;

This "frustrated outer join" query will find all records in Table1 which have
no match in table2, and (if you have defined a relationship) will be
updateable. Try it this way first and then change the word SELECT to DELETE
and run it... you DID make a backup, *right*?

John W. Vinson [MVP]