Deleting only part of the duplicates. I want to retain one of them in my table

[Jessica240]

Hello everyone. Finding duplicates and deleting duplicates is rather simple. However, I'm trying to run a query that only deletes the extra copies of my duplicates while leaving one in the table. Right now when I run my delete query, it deletes ALL of the duplicates when I really need to keep at least one of those records.

Here is my current SQL Statement:

DELETE Hats.*, Hats.PersonsName
FROM Hats
WHERE (((Hats.PersonsName) In (SELECT [PersonsName] FROM [Hats] As Tmp GROUP BY [PersonsName] HAVING Count(*)>1 )));

I have a HUGE table that lists customers names and how many hats they've bought. All I need is to know how many customers I have had over the past quarter, so I just need a count of each individual customer. If a customer has bought hats from me on more than one occasion, there will be multiple records in my table for each transaction. This will cause the customers name to be listed more than once.

If I try to count the number of customers in my table, I will end up with a number too high because the count will include some of the same customers more than once. I can run a find duplicate query and delete them by hand, but the table is too large to delete them one by one.


Does anyone know of a good SQL that would solve this problem? Any help would be greatly appreciated! Thanks in advance!

[Jessica240]

I found an article that explains how to do this! Here is the link in case anyone else has this same problem.

http://www.bluemoosetech.com/microso...licate+Records

[rpeare]

Why are you deleting data? If you are trying to get information out deleting the data is the worst possible idea, you can get a list of unique clients using queries.

SELECT [PersonName] FROM Hats GROUP BY [PersonName]

Will give you a list of unique clients in your table.

SELECT [PersonName] FROM Hats WHERE [SaleDate] between #1/1/2014# and #12/31/2014# GROUP BY PersonName

Should give you a list of clients between the two sdates listed.

Just create an aggregate query (look for the sigma button that looks like a capital E on your toolbars) and click it, the TOTALS row just make sure the DATE says WHERE and not GROUP BY and it will correctly give you a list of unduplicated clients.

[Jessica240]

Sorry I'm fairly new to Access so I wasn't aware that was even a possibility. Once again, you've saved the day!

Thank you so much! I am extremely grateful!