Computer Help forum

General discussion

Automatically Delete Duplicated Data from a Table

by faxylady / April 6, 2004 4:26 AM PDT

Automatically Deleting Duplicated Data from a Table 040104

Using 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
You are posting a reply to: Automatically Delete Duplicated Data from a Table
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Automatically Delete Duplicated Data from a Table
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Re:Automatically Delete Duplicated Data from a Table
by HerbChong / April 7, 2004 10:05 AM PDT

you missed a step. it doesn't say copy and paste the records, it says copy and paste the structure. you then run an append query selecting unique records to copy the first occurrence of each key in the original table into the new table.


Collapse -
Re:Re:Automatically Delete Duplicated Data from a Table
by faxylady / April 10, 2004 4:45 AM PDT

Thanks for responding. Please explain in detail. If you can, use the copy of the help file and list the step by step solution. For example, at Step 5 enter...

I am a novice to Access.

Thanks again.

Collapse -
Re:Re:Re:Automatically Delete Duplicated Data from a Table
by HerbChong / April 11, 2004 2:17 AM PDT

one of the firt five steps says Paste the structure of the table. it's in the instructions, so reread them. you are pasting the table, not the structure. when you paste, it asks you what you want to paste and the instructions says paste the structure. this forum isn't for detailed instructions, it's a place to get basic help and suggestions for things to try to resolve a problem. if you need very detailed Access instructions, you need to head over to the Microsoft Office or Access Forum at


Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?