Delete Query with Join

[conleyle]

I hope this is an easy one, but I can't figure it out even after lots of googling. I'm trying to run a delete query where two tables (T_Instrument_Registry and T_CustomerList) are joined by [Cust_Rep_Office] and [Distributor #]) and match. But I keep getting an error to "Specify the table containing the records you want to delete." Any help would be greatly appreciated.

Here is what I have.
DELETE T_Instrument_Registry.[Distributor #], T_Instrument_Registry.Segment, T_Instrument_Registry.Item, T_Instrument_Registry.Cust_Rep_Office, T_Instrument_Registry.Qty, T_Instrument_Registry.[Initial or Update], T_Instrument_Registry.[Date Updated], T_Instrument_Registry.[Previous Distributor], T_Instrument_Registry.Comments

FROM T_Instrument_Registry INNER JOIN T_CustomerList ON (T_Instrument_Registry.[Distributor #] = T_CustomerList.[Distributor #]) AND (T_Instrument_Registry.Cust_Rep_Office = T_CustomerList.Cust_Rep_Office)

WHERE (((T_Instrument_Registry.Cust_Rep_Office) In (SELECT [T_Instrument_Registry].Cust_Rep_Office

FROM [T_Instrument_Registry] LEFT JOIN [T_CustomerList] ON [T_Instrument_Registry].Cust_Rep_Office = T_CustomerLIst.Cust_Rep_Office

WHERE ((([T_CustomerList].Cust_Rep_Office) Is Null)))));

 

[Joe4]

Delete Queries can be a little tricky, especially if multiple tables are involved.
What I like to do is first create a query that identifies the records I want to delete. Which field (or fields) uniquely identifies the records to be deleted?
Then I nest that in the WHERE clause like this:

DELETE [Table1].*
FROM [Table1]
WHERE [ID_Field] in
(SELECT ID_Field
FROM...
...)

So the blue text is the Query Code for the query identifying the records to delete.

Note, that since you are deleting the WHOLE record, there is no need to list out each field after the word DELETE in a Delete Query. You are deleting the whole record, not just certain fields in that query.

 

[conleyle]

Thanks. This is what I ended up with, and it worked.

 

[conleyle]

I just found a super easy way to do this and thought I would share in case anyone else was looking. I set up my Select Query linking the two tables. Then, I opened up the Property Sheet for the query and changed Unique Records to Yes. And it worked. No SQL needed. Sweet.

 

[Joe4]

I just found a super easy way to do this and thought I would share in case anyone else was looking. I set up my Select Query linking the two tables. Then, I opened up the Property Sheet for the query and changed Unique Records to Yes. And it worked. No SQL needed. Sweet.

Note that is NOT the same as a delete query. That just suppressed/hides duplicate records. It does NOT physically delete any records like a delete query would.
If you just hope to suppress duplicate records, there are a few ways of doing it. One way is the way you described. Another way is to use an Aggregate (Totals) Query.