Delete Query using linked table

[Guest]

I have certain criteria in one table and want to linkt it another table and
delete the matching records, the criteria matches three fields. Can Access do
this? I have tried but I get a meesage saying "cannot delte from specified
table".
The syntax is:
DELETE States.*
FROM Regions INNER JOIN (States INNER JOIN RegionAssignment ON
States.Abbreviation = RegionAssignment.Abbreviation) ON Regions.RegionID =
RegionAssignment.RegionID;
What am I doing wrong?
TIA for your help
Joe

 

[John Spencer]

DELETE DistinctRow States.*
FROM Regions INNER JOIN (States INNER JOIN RegionAssignment ON
States.Abbreviation = RegionAssignment.Abbreviation) ON Regions.RegionID =
RegionAssignment.RegionID;

IF that fails, you may have to use a subquery in a where clause

DELETE DistinctRow States.*
FROM STATES
WHERE States.Abbreviation in
(SELECT RegionAssignment.Abbreviation
FROM RegionAssignment INNER JOIN Regions
ON RegionAssignment.RegionID =Regions.RegionID)

As always TEST on a COPY of your data to make sure this does what you want
it to do.