Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Automatically Delete Duplicated Records from a TAble

Apr 7, 2004 5:58AM PDT

Automatically Deleting Duplicated Data from a Table 040104

Microsoft ACCESS 2000


A project that I am working on requires deletion of duplicated data from a table. The table consists of only 1 field named Email Addresses. My plans are to use the email addresses for sending email. I have used the text data type instead of the hyperlink data type because I wish to perform sorting operations alphabetically on them periodically as I continue to enter the email addresses. So far, I have collected over 800 email addresses from various sources. The hyperlink data type inhibited me from using the sort feature.

While collecting the data from various sources, many duplicates are produced. Therefore, I turned to the help files and located the topic, Automatically Delete Duplicate Records from a Table. Every effort has been made to follow the instructions meticulously.

Steps 1 ?5 are fairly simple, creating a new table by copying and pasting in the Database window.. Step 6 states to ?Open the new table in Design view, and select the field(s) that contained duplicates in the table you copied.? No problem here.

Step 7, which states, ?Click Primary Key on the toolbar to create a primary key based on the selected fields,? proceeds okay.

The difficulty begins with Step 8, which says to ?Save and close the table.? When ?Yes? is clicked, an error message appears stating, ?The changes you requested to the table were not successful because they would create duplicate values in the Index, Primary Key, or relationships. Change the data in the field or fields that contain duplicate data, remove the Index, or redefine the index to permit duplicate entries and try again.?

The second part of this help file is To append only unique records to the new table.

Step 1, Create a new query based on the Original table containing duplicates.

Step 2, In query Design view, click the Query Type on the toolbar, and then click Append Query.

Step 3, In the Append dialog box, click the name of the new table from the Table Name list, and then click OK.

Step 4, Include all the fields from the original table by dragging the asterisk (*) to the query design grid.

Step 5, Click Run on the toolbar.

Step 6, Click Yes when you receive the message that you?re about to append rows.

Trouble begins here with Step 7, Click Yes when you receive the message that Microsoft Access can?t append all the records in the append query. This transfers only unique records to your new table and discards the duplicates. This step does not appear. No error message appears.

Steps 8 and 9 to see the results cannot be performed.

The Office 2000 version of Access is being used on a Pentium III, 500 Mg machine with a 120gg hard drive. Windows 98 is the operating system.

At present, I do not have Internet access. I have to use public computers available from libraries.

Please help me to delete the duplicated records from my table.

Thank you.

Discussion is locked

- Collapse -
Leave them duplicated in the table.
Apr 7, 2004 7:49AM PDT

Faxylady,

Make a query like
SELECT DISTINCT emailaddress FROM mytable
and use that query as a source for your unique email-addresses. Everything you can do with a table you can do with a query, so it doesn't really matter that somewhere in the very recent history of the data they were not unique. They are unique as the query produces them, and that's all that matters.

It's a simple SQL-trick. However, you can't do it from the GUI, I think, so you'll have to type it in the SQL-view, but that's easy enough.

If you like, you can use this query to make a new table with your unique email-addresses by converting it into a create table query and running that, but it offers no clear advantages, I should say.

It goes like:
SELECT DISTINCT emailaddress INTO mynewtable FROM mytable

Do you know the VBA-coding to send emails directly from Access? Or are you planning to export the addresses to a text-file and copy that into the BCC-fields of a mail? Or did you figure out a third way? Let us know.

And be sure you're not accused of spamming. This forum certainly won't help you with that. So, if you've got more questions, please explain why you think your purpose of sending mail is legitimate.


Hope this helps.


Kees

- Collapse -
Re:Leave them duplicated in the table.
Jun 3, 2004 2:40AM PDT

EASY!
Curser in Cell A-1 (assuming it's your header: E-mail)
Data > Advance Filter >
Check "Copy to Another Location" AND "Unique Records Only"
List Range: A1:A ___ (whatever last row number is)
Copy to: B1
O.K.
After it's done - delete Column A. No more dups

- Collapse -
Two tips for the future.
Apr 7, 2004 6:29PM PDT

1. Once you have a table with unique email-addresses (made, for example, by my second query) make the only field a primary key. That has two advantages: (a) the table is always presented sorted when you open it, and (b) it's impossible to add new duplicates.

2. You might consider switching from Access to Excel. Two ways to unduplicate rows in Excel are discussed in http://reviews.cnet.com/5208-6129-0.html?forumID=29&threadID=16203&messageID=180005

Hope this helps.


Kees

- Collapse -
Re:Automatically Delete Duplicated Records from a TAble
Jun 3, 2004 2:41AM PDT

Sorry: my solution was for Excel not Access