Deleting duplicates with leaving the most current date

[haydenbl]

Hello All,



I am using Access 2007-2010. I have a table that contains a member number and a transaction date. I need to remove the duplicate while leaving the record with the most current date. I know that I can use a query to write out the data by saying the member number is unique, but how do I determine which is the most current date?

Thanks

[rpeare]

Why are you deleting information, that's a pretty dangerous practice. The whole point of having a database is that you have the ability to look at historical and current data with equal ease.

It would be a far better practice to just design a query that showed you only the most recent record(s) by date.

[haydenbl]

Why are you deleting information, that's a pretty dangerous practice. The whole point of having a database is that you have the ability to look at historical and current data with equal ease.

It would be a far better practice to just design a query that showed you only the most recent record(s) by date.

I would be alright if I didn't delete the data, I just need a query that would only show me the data without the dups with the most current date shown.

[rpeare]

does your 'duplicate' have identical data for all fields, or does it vary from record to record?

So let's say you have MEMBER 1, but they have two records on 1/1/2013, how are you determining which is the 'correct' record to show?

is there also a time in the date field i.e. 1/1/2013 2:00:00 PM?

should there NEVER be a duplicate date for the same member?

If there should never be a duplicate is there a reason you allow it in your table design?

it's easy to find the most recent date for any particular member

SELECT MEMBER, MAX(DATEFIELD) AS MostRecentDate FROM tblTableName GROUP BY MEMBER

This'll show you the most recent date of activity for each member.

[haydenbl]

Thank you that is exactly what I am needing. I appreciate the help.