Delete query

[timothyl]

Hello, I have a table( about 11,000 Entries) with insurance info. It can some times happen I will have entries like so:

Id Payor Position Insurance

aaaa 1 ZRB
aaaa 2 MA
aaaa 3 MA

I need to delete the payor position 3, have tryed

DELETE * FROM tblInsuranceRehab WHERE (CustomerID and PayorPosition = 2 and insurance) = (CustomerID and PayorPosition = 3 and insurance)

Dose not work delete's every thing but payor position =1, any Ideas on how to progress, thanks

 

[John Big Booty]

Try;

DELETE tblInsuranceRehab.Postion
FROM tblInsuranceRehab
WHERE (((tblInsuranceRehab.Postion)=3));

This will delete ALL records in which Position = 3

 

[timothyl]

Thanks for your Response John Big Booty(they shoulda made a sequel), I just reread my question and I was far from clear. What I meant to ask was that for any ID, I need to delete position = 3 Where Position = 2 And Position = 3 are equal.

 

[John Big Booty]

OK sorry I misunderstood, try;

DELETE Distinctrow tblInsuranceRehab.*
FROM tblInsuranceRehab INNER JOIN Query2 ON  tblInsuranceRehab.Payor = Query2.Payor
WHERE (((tblInsuranceRehab.Postion)=3));

were Query2 is;

SELECT tblInsuranceRehab.Payor, Max(tblInsuranceRehab.Postion) AS MaxOfPostion, tblInsuranceRehab.Insurance, Count(tblInsuranceRehab.Insurance) AS CountOfInsurance
FROM tblInsuranceRehab
GROUP BY tblInsuranceRehab.Payor, tblInsuranceRehab.Insurance
HAVING (((Count(tblInsuranceRehab.Insurance))>1));

 

[timothyl]

Thanks John Big Booty, I got that to work