Help deleting related rows based on comparing values in a count field.

[Patrick1977]

I have data like this:

A/D Date FLD TrCnt
A 02/24/2010 A04 1


A 03/21/2010 H04 1
A 07/26/2010 A04 3
D 07/26/2010 A04 2
A 07/07/2010 H05 2
D 07/07/2010 H05 3
A 09/22/2010 R05 1
D 09/22/2010 R05 1


A/D stands for add and delete. Essentially, I want to keep Add (A) rows only under certain situations....

IF TrCnt for A > TrCnt for D with same Date/FLD combo, then keep the A row with that combo of Date and FLD (always delete all D rows)
But if TrCnt for A <= that for D ... Delete the A and D records for that combo of Date and FLD

The results for the above example would be:

A/D Date FLD TrCnt
A 02/24/2010 A04 1
A 03/21/2010 H04 1
A 07/26/2010 A04 3

Please let me know if you have any ideas doing this using SQL or VBA code.
Thank you!!

[CJ_London]

something like this looks like it will do the job

SELECT Adds.*
FROM Table2 AS Adds
WHERE Adds.AD="A" and not Exists (SELECT * FROM Table2 AS Dels WHERE AD="D" and aDate=Adds.adate and fld=adds.fld and trcnt>=adds.trcnt)

[CJ_London]

and if you want to delete the unrequired rows then the code would be something like

DELETE *
FROM Table2 AS Adds
WHERE (AD="A" AND Exists (SELECT * FROM Table2 AS Dels WHERE AD="D" and aDate=Adds.adate and fld=adds.fld and trcnt>=adds.trcnt)) OR (AD="D")

[Patrick1977]

Excellent!
Seems so obvious after seeing your code.
Thank you!

something like this looks like it will do the job

SELECT Adds.*
FROM Table2 AS Adds
WHERE Adds.AD="A" and not Exists (SELECT * FROM Table2 AS Dels WHERE AD="D" and aDate=Adds.adate and fld=adds.fld and trcnt>=adds.trcnt)