SQL or VBA?

[cpc]

Good day,

I have a simple table (or so I thought) attached. It has 5 columns (Ref, Revision,Name, Description, Data). I want to create a query where the duplicate records (defined as rows with identical [Reference] AND [Description]) are compared, and the older (the record with older [Data])
record is removed.
I've been unsuccessful trying SQL for this. It probably requires VBA where i am unfortunatley still a novice. Any assistance is appreciated.

Thank you

 

[David R]

You want to play with Totals Query (the sigma in the ribbon, in Design View). Reference and Description need to be set to Group By, and [Data] needs to be DMax.

 

[CJ_London]

This query will delete all the older records. Note that Name is a reserved word and its use can cause unexpected issues so although not used here, I recommedn you change it

DELETE Table1.*, [data]=(Select max(data) from table1 as tmp where reference=table1.reference and description=table1.description) AS Expr1
FROM Table1
WHERE ((([data]=(Select max(data) from table1 as tmp where reference=table1.reference and description=table1.description))=False))

 

[cpc]

This query will delete all the older records. Note that Name is a reserved word and its use can cause unexpected issues so although not used here, I recommedn you change it

DELETE Table1.*, [data]=(Select max(data) from table1 as tmp where reference=table1.reference and description=table1.description) AS Expr1
FROM Table1
WHERE ((([data]=(Select max(data) from table1 as tmp where reference=table1.reference and description=table1.description))=False))

CJ,
You made that look so easy! You're awesome!THANK YOU!

 

[CJ_London]

no problem pleased to help