MS ACCESS Delete Dups based on criteria 1

[LuckyDanger]

I have a table in which I have the following fields:

Row Lead_Code Lead_Ref Name

1 123 123A Another
2 123 123B Another
3 125 125A OJ
4 125 125B OJ
5 127 127A Keep

I want to keep the rows where Lead_Ref has the last character of B as this was the latest instance. The Lead_Ref ending in A can be eliminated where the Lead_Code is the same as the Lead_Ref with B:

Thus removing rows 1 & 3.

Is there any VBA code I can run in an access query to return the data less removed rows?

Many Thanks for your advice

 

[PHV]

No VBA required:

SELECT A.Row, A.Lead_Code, A.Lead_Ref, A.Name
FROM tblLead AS A INNER JOIN (
SELECT Lead_Code, MAX(Lead_Ref) AS Max_Ref FROM tblLead GROUP BY Lead_Code
) AS M ON A.Lead_Code = M.Lead_Code AND A.Lead_Ref = M.Max_Ref

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LuckyDanger]

Slow but works a treat, many thanks.