X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Deleting Entire Rows If One Field Is Duplicated, Access 2016    
post Feb 1 2018, 04:48 AM

Posts: 62
Joined: 29-January 17

Good Morning,
I have a table that holds email addresses for workers in different workplaces.
I need to send out a single email to each worker using a home-grown email server that does not take out duplicate email addresses. I have to sort that myself.
As may be seen from the table below some work in more than one office.
I need to delete the ‘extra’ email addresses which are duplicated/triplicated, whilst keeping the other fields in the record intact.

This is table t_Email_FullDetails
Attached File  t_Email_Full_Details.JPG ( 56.38K )Number of downloads: 7

What I hope to get is a table looking like this, either the original table manipulated to achieve my needs, or for a new table to be made.

This is table t_Email_FINAL

Attached File  t_Email_FINAL.JPG ( 41K )Number of downloads: 1

When I started this, I thought that it was ten minutes work to build nested recordsets to achieve this end. But I keep on coming up against an error 3167 record deleted.
I also tried SQL Select distinct etc … but a answer continues to elude me.
Any help will be greatly appreciated.

Go to the top of the page
post Feb 1 2018, 07:34 AM

Posts: 392
Joined: 20-September 12
From: Cornwall UK

There is a find duplicates query, in the Query Wizards. on the create Tab in 2010. Hopefully it's similar in 2016.

Run it against email address. in the first table.

Be sure to leave one of each in the query. ie don't delete all of them, just the duplicates.

Hope that helps, with the first part.
This post has been edited by BuzyG: Feb 1 2018, 07:44 AM
Go to the top of the page
post Feb 1 2018, 08:06 AM

UtterAccess VIP
Posts: 10,365
Joined: 10-February 04
From: South Charleston, WV

I realize that you have an immediate problem but you should realize that the best way in the long run to deal with this issue is with proper design. If your tables are set up correctly, you won't have to delete duplicates. There should be a table with the e-mail recipients in it. It could be a customers or employees or a people table or something of that sort. There is only one entry per recipient in this table and you use it to produce the e-mails. No elimination of duplicates necessary.
Go to the top of the page
post Feb 1 2018, 08:46 AM

UA Admin
Posts: 34,123
Joined: 20-June 02
From: Newcastle, WA

First, I agree with Robert. This is one of those table normalization and design problems masquerading as a "how do I" problem. Fix that underlying problem at the table design level and this problem is easy to address.

That said, I have some other thoughts along the same lines.

Are these truly duplicates? In the screen shot it looks like the different copies of an email address are associated with different locations. If you physically delete any one of them, don't you also delete that location? Is that a good thing?

If all you want is a list of People and Email Addresses, you could use an aggregate query to do that, using GROUP BY on the name and email address fields only. That would avoid the need for dangerous deletion of records, or worse, proliferating copies of tables.

Again, fixing the table design problem is going to be the most effective thing to do, though, both here and in other, similar, situations.
This post has been edited by GroverParkGeorge: Feb 1 2018, 08:47 AM
Go to the top of the page
post Feb 1 2018, 10:05 AM

Posts: 62
Joined: 29-January 17

The problem that I had is would that the DISTINCT seemed to delete all the same address emails, ... it wouldn't leave one behind ... so I couldn't make it work for me.

Yes agreed. the problem is that each branch send me an undated list every week or so, so I have 30 odd lists to amalgamate into one table before I can even start with the problem. Having said that, I had forgotten to set the index for the email address to No Duplicates, which, when put right, solved my problem.
So obvious when one's head is in gear. I was over-engineering the problem by a factor of at least 10! Many thanks for finding the solution that I will use.

I do delete the location names for my final table, leaving just names and email address to forward to the email server. Your GROUPBY solution is elegant, and I would have tried that had I not been put on the track of the indexing problem.

Thank you all for coming to my rescue.
I appreciate the thoughts and time put into solving my problem for me.

So embarrassing really !!!

Go to the top of the page
post Feb 1 2018, 10:19 AM

UtterAccess VIP
Posts: 10,365
Joined: 10-February 04
From: South Charleston, WV

Glad you got it working.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    15th December 2018 - 07:20 AM