Removing duplicate records

[J_Orrell]

Guys I'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.

This is an example of the data in my table:

Ref	Date		Status
1130071	11/06/2015	Do Not Pay
1144543	06/07/2015	Do Not Pay
1157607	14/05/2015	Do Not Pay
1165207	05/05/2015	Do Not Pay
1176701	08/05/2015	Do Not Pay
1180362	18/05/2015	Do Not Pay
1185170	15/05/2015	Do Not Pay
1787340	20/07/2015	Do Not Pay
1828099	15/07/2015	Do Not Pay
1842260	10/07/2015	Payment Due
1842260	22/07/2015	Payment Query

As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015). You wouldn't think it would be rocket science. Thanks.

 

[plog]

You need a subquery. Find out the latest date for each Ref, then link that query back into your table to pull the records where the LatestDate=Date and Ref=Ref.

Also, 'Date' is a poor choice for a field name. It's a reserved word (https://support.microsoft.com/en-us/kb/286335) and makes writing code and queries a little more complicated. I suggest you prefix the name with what it represents (StatusDate).

 

[J_Orrell]

Thanks I'll give it a go