How to remove duplicates from an Access table using SQL

Question

I’m responsible for a non-profit’s annual fundraiser in which people can send a product to a list of receivers at a fixed cost per receiver. Each receiver gets only one product, no matter how many senders are sending to them, along with a card listing the names of those who sent to them.

 

We also support receiver lists, such as our board members. A sender can send to, in addition to a list of individuals, a list, which results in that sender sending to everyone in that list, for a flat (discounted) fee.

 

I’ve got a database in a Microsoft Access mdb file, driven by a VB program.

As the final part of the distribution process, a table is created which includes four fields:

THIS_YR:            True if this order is to be processed. (FALSE denotes a prior order that the sender decided not to renew.)

SENDER:            The key to a record with all the sender’s information.

RECEIVER:         The key to a record with all the receiver’s information.

SOURCE:            The key of a list responsible for creating this order. For example, if key “gim” is sending directly to key “art”, the source will be null. If “gim” is also sending to the list whose key is “ard”, and “art” is a member of the “ard” list, that order will show up as sender “gim”, receiver “art”, source “ard”.

 

At this point in the process, the table has been built, showing all the individuals sending and receiving the product. All the THIS_YR=false have been removed. The following list is a sample of the table. I no longer care about the “THIS_YR” column (they’re all true by now), nor the “SOURCE” column (the recipient won’t know whether he was chosen to receive directly or via a list). All I want to do is remove all the duplicate SENDER/RECEIVER lines.

 

Given the following input, the table should end up with only one SENDER=“gim”/RECEIVER=”art” line; all the other lines should be included.

 

THIS_YR	SENDER	RECEIVER	SOURCE
THIS_YR	SENDER	RECEIVER	SOURCE
TRUE	gim	art	ard
TRUE	gim	bra	ard
TRUE	gim	cim	ard
TRUE	gim	fin	ard
TRUE	gim	fre	ard
TRUE	gim	add	nts
TRUE	gim	and	nts
TRUE	gim	ann	nts
TRUE	gim	ara	nts
TRUE	gim	aro	nts
TRUE	gim	art	nts
TRUE	gim	ash	nts
TRUE	wei	gol
TRUE	its	bue
TRUE	its	low
TRUE	its	gor
TRUE	its	bro
TRUE	its	can
TRUE	sol	art	ard
TRUE	sol	bra	ard
TRUE	sol	cim	ard
TRUE	sol	fin	ard
TRUE	sol	fre	ard
TRUE	wei	ash
TRUE	its	cap

 

Rather than plowing through the list line by line, I’m looking for an efficient way to remove any duplicate SENDER/RECEIVER lines using a SQL command from VB. I found several good solutions on the web, but none were viable for Access. Ken Sheridan’s suggestion in https://answers.microsoft.com/en-us/msoffice/forum/all/access-database-how-to-delete-duplicate-records/9c68feff-b557-4c76-814d-671e392c0d6f looked good, my table doesn’t include a numeric primary key (ContactID), and I don’t want to add it.

 

Any simple solutions? Thanks.

Answer 1

Hi there Rich Goldberg

Thnx for using QandA platform

To remove duplicate SENDER/RECEIVER rows in Access, use a DELETE query like this:

WHERE ID NOT IN (SELECT MIN(ID) FROM YourTable GROUP BY SENDER, RECEIVER); If your table lacks an ID, create a temporary table with an auto-number field or use SELECT DISTINCT to extract unique rows into a new table. Then delete and re-insert the cleaned data into the original table.then duplicates are removed efficiently without altering your schema.

If this helps kindly accept the answer thanks much.