[HELP] Removing duplicates but keeping the lastest record

[ongjx]

Hi, I am new to access and really require some help with access. So currently i have queries and tables with several duplicated "username" records. What should i do to remove the record with a smaller value of "win/loss" and keep the duplicated "username" with a bigger value of "win/loss". Thanks in advance for your help!

[June7]

How many records are involved? The easiest approach is to do a sort and then review the records and manually delete the dupes. Automating this in a query will be tricky. It would be easier if there is a unique record ID in the table. An autonumber type field can serve. Try TOP N parameter to pull the record with lowest Win/Loss for each username. Review http://allenbrowne.com/subquery-01.html#TopN

DELETE FROM [database with duplicates] WHERE ID IN (SELECT TOP 1 ID FROM [database with duplicates] AS Dupe WHERE Dupe.Username=[database with duplicates].Username ORDER BY Dupe.Username, Dupe.[Win/Loss])

Be sure to test with a copy of the table.