Removing Duplicate Entries with Multiple Criteria

[Funky Mayo]

Hey, I just started migrating my excel spreadsheet into Access 2016 and I ran into a problem. I want to delete every customer entry besides for the completely unique ones.

Ex.

123 K Ave	Susy	Green
123 K Ave	Susy	Green
123 K Ave	Susy	Blue
123 K Ave	Fred	Blue
234 L Ave	Fred	Blue
234 L Ave	Fred	Green
234 L Ave	Susy	Green

I want to keep everything here since they are unique data sets except for the second row because it is completely identical to the first row. I've been able to delete if the addresses are identical but how do I filter based on the rest of the criteria as well? I do not want to run multiply queries comparing every possible color and name manually either. I want to have it automatically compare and delete completely identical entries but leave the rest.

Thanks for the help!

[Bob Fitz]

see this link: https://www.techrepublic.com/article...-access-query/

[Funky Mayo]

see this linl: https://www.techrepublic.com/article...-access-query/

I tried to specify what I wanted because I don't want what this guide is saying. It ultimately filters so there is only 1 address left of each. I am including people and different things about them as separate entities I don't want to remove.

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[Bob Fitz]

I tried to specify what I wanted because I don't want what this guide is saying. It ultimately filters so there is only 1 address left of each. I am including people and different things about them as separate entities I don't want to remove.

If you follow the guide but create a Composite Primary Key that comprises of the three fields you specified in the new blank table, then you should get the desired results

[davegri]

Try this. The table is required to have a primary key autonumber, like Member_PK in the example.


and the query sql:

DELETE *
FROM tblMembers AS T1
WHERE Member_PK <> 
 (SELECT MAX(Member_PK)
 FROM tblMembers AS T2
 WHERE T2.addr = T1.addr AND T2.FirstName = T1.FirstName AND T2.LastName = T1.LastName);