Automatically Delete duplicated Data from a table

F

Faxylady

Automatically Deleting Duplicated Data from a Table 040104


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.
 
R

Robin Proctor

Create a new table (Table2) with the same field structure as the
existing one (Table1)

Create a query (in SQL view) with:

INSERT INTO Table2
SELECT DISTINCT * FROM Table1

and run this query.

You will now have Table2 containing unique email addresses.

Robin Proctor
 
F

Faxylady

Thank you for your reply. I know nothing about SQL
Statements. Please tell me how to 1. Create the query (in
SQL view) with:

INSERT INTO Table2
SELECT DISTINCE * FROM Table 1

I am just learning ACCESS. Thanks again for your kind
response.
-----Original Message-----
Create a new table (Table2) with the same field structure as the
existing one (Table1)

Create a query (in SQL view) with:

INSERT INTO Table2
SELECT DISTINCT * FROM Table1

and run this query.

You will now have Table2 containing unique email addresses.

Robin Proctor
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.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.copying and pasting in the Database window.. Step 6
states to â?oOpen the new table in Design view, and select
the field(s) that contained duplicates in the table you
copied.â? No problem here.toolbar to create a primary key based on the selected
fields,â? proceeds okay.oSave and close the table.â? When â?oYesâ? is clicked,
an error message appears stating, â?oThe 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.â?receive the message that Microsoft Access canâ?Tt 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.Pentium III, 500 Mg machine with a 120gg hard drive.
Windows 98 is the operating system.
 
R

Robin Proctor

Create a new query, choosing Design View. Close the Show Table box
without adding any table. You are now in a blank query in Design View.
With the top left button on the toolbar you can change to SQL View. It
will probably look like a blank sheet of paper, maybe with SELECT
written in the top corner. This is where you write your SQL statements,
and is an alternative to Design View.

Write into here the SQL below, using your own table names instead of
Table1 and Table2. Close and save the query with some name of your own
choosing. It should have a MakeTable icon in the Queries list in the
main database window. Try to run it from here by choosing Open.

Regards

Robin Proctor
 
K

Kentucky

An easier way to do this is to right mouse click the table
in the window do a copy and paste, just paste the
structure. Go into the new table created and make the id
the primary key.
Then do a copy by right mouse clicking again and do a
paste and a dialog box will come up and say append data
and you will have to enter the new table name. It will
only import the data that is not duplicates.
-----Original Message-----
Create a new table (Table2) with the same field structure as the
existing one (Table1)

Create a query (in SQL view) with:

INSERT INTO Table2
SELECT DISTINCT * FROM Table1

and run this query.

You will now have Table2 containing unique email addresses.

Robin Proctor
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.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.copying and pasting in the Database window.. Step 6
states to â?oOpen the new table in Design view, and select
the field(s) that contained duplicates in the table you
copied.â? No problem here.toolbar to create a primary key based on the selected
fields,â? proceeds okay.oSave and close the table.â? When â?oYesâ? is clicked,
an error message appears stating, â?oThe 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.â?receive the message that Microsoft Access canâ?Tt 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.Pentium III, 500 Mg machine with a 120gg hard drive.
Windows 98 is the operating system.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top