delete query

  • Thread starter Jose E via AccessMonster.com
  • Start date
J

Jose E via AccessMonster.com

Hi gurus.

I would like to create a delete duplicate query that deletes a conditional
critiria. I will explain

I have a table with a few fields

doc_num ;doc_type ;doc_date
01 pt xx
01 py xx
01 ww xx
02 pt xx
02 py xx

I would like to delete ALL records that are duplicate but only if they are
equal to doc_type of (PY/PT/WW), in this case only doc 01 would be deleted
not doc 02 as it does not have all the types.

Thanks
 
J

John Spencer (MVP)

Save the following as QueryA:
SELECT DISTINCT Doc_Num, doc_type
FROM YourTable

Save the following as QueryB:
SELECT Doc_num
FROM QueryA
Where Doc_type in ("PY","PT","WW")
GROUP BY Doc_Num
HAVING Count(Doc_Num) = 3

Now write a third query using queryB
DELETE DistinctRow YourTable.*
FROM YourTable INNER JOIN QueryB
On YourTable.Doc_num = QueryB.Doc_num

You may not need query A if Doc_type values are not repeated for any doc_num.
This could all be done in one query if repeats aren't allowed.


DELETE DistinctRow YourTable.*
FROM YourTable
WHERE Doc_Num IN (
SELECT T.Doc_num
FROM YourTable As T
Where T.Doc_type in ("PY","PT","WW")
GROUP BY T.Doc_Num
HAVING Count(T.Doc_Num) = 3)
 
Top