Need to delete unmatched

[Josh]

Have two tables, one with salesmen basic info, and another with salesmen's
history. The salesmen's history goes back a long time, the salesmen basic info
is pretty recent. I don't care about history on non-current salesmen, and want
to delete from the history table any record where salesmenID doesn't match any
SalesmenID in Basic info table. I can make a query to *find* the records that
don't match, but how do I delete them, because of course, the joined tables in
the query won't let delete anything.

Have a feeling this is pretty simple, but just can't think of how right now.

Thanks, Josh

 

[John Spencer]

Please post the SQL you have now to find the records. It is probably a
simple change of two to make it work as a delete query

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

If you are using the query grid, you can try the following.
Open your query
UNCHECK the show property for all the fields
Check the show property for ONE of the fields in the table you want to
delete
In the query properties, set Unique records to Yes.

 

[Josh]

Wasn't able to get your suggestion about unchecking Show Property in query grid
to work, but was finally able to accomplish what I wanted by only querying the
table that had the records I wanted to delete, adding a dlookup field to lookup
the salemens from the other table. Then, simply delete the records where the
dlookup field was blank.

I did this on a backup copy, now I'll look in more detail to make sure it did
what I wanted.

Thanks