Software

Ask something
Discussion Locked

Removing Duplicated Entries

0 helpful
10 replies

by borginva

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We use a program called Clientele. It is upsized to SQL 2000, but we export the data into Access 2000. Something happened and 1000 calls were duplicated. When I try to remove them in SQL 1 by 1, it says the data is linked and if I want to remove it also. Saying yes was bad!
Is there a way, some query or something, to remove the data easily without loosing the original?

10 replies

by sgt_shultz

insufficent data. (sorry, couldn't resist!)
well, i am not an sql programmer so can't help much. (try the php and mysql sites, those users are the greatest!). if it were my nut to crack i'd probably try to fix it in access...can you look at the data in Access and view Tools/Relationships and see how the tables are related? sounds like cascaded deletes are turned on...that deletes the record and any linked ('related') to it...you can turn that off and try your deletes but they have that referential integrity on there for a reason so best to study the table relationships first so you can see what may get screwed up...sorry this is not helping but we need more info about your exact situation...i.e. are you comfortable manipulating the db outside of sql 1 x 1?
in case this helps, the way to get rid of dups in access is to create a duplicate table but frame only, no data. then set the primary key to the duplicated field. then import from the old table with the dups into the new table. since the primary key can't have duplicates, access will drop the second duplicated record...then you rename the table and change the primary key back to what it was...

by borginva

I know more about SQL than I do about Spain: nothing.
I can not find a way to easily, without messing up our data, to remove duplicated rows in our SQL 2000 Server. The filed I am searching by is CALL_NUM.
Does anyone have some scirpt or something that will search for dups and remove ONLY ONE entry?

by sgt_shultz

okey dokey. try this: export to Access, use the Find Duplicates Wizard (under Querys). then, delete 'em and export back to your database...

by borginva

I would but eveyrthing is upsized to SQL 2000. I actually got some coding Friday for SQL to try.

by borginva

PART 1

This was the working result:

To remove the duplicate calls we had again (1000+), I exported all the SQL data to access, used the M$ procedure below, then upsized the data back in.

Microsoft Access 2000
This article was previously published under Q209183
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY
This article describes how you can remove duplicate records from a table by using primary keys. By using this method, you can check for duplicate values in up to 10 fields in the table.

MORE INFORMATION
To remove duplicate records from a table, follow these steps:

Make a copy of the structure of the table from which you want to remove the duplicate records.

To copy a table:

Select the table in the Database window

On the Edit menu, click Copy.

On the Edit menu, click Paste.

Enter a name for the new table.

Select Structure Only

Click OK.

Open the new table in Design view.

Select the field(s) that contain the duplicate values.

To make your selection the primary key for the table, click the Primary Key button on the toolbar.

Save and close the table.

by borginva

PART 2

Create an append query based on the original table containing duplicates.

In the query Design View, on the Query menu, click Append Query.

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

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

On the Query menu, click Run.

Click Yes in the dialog box advising you that you are about to append records.

Because the Primary Key field(s) in the new table will not accept duplicate values, the following error message will be displayed:

Microsoft Access can't append all the records in the append query.
Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes. For an explanation of the causes of the violations, click Help.

Click Yes.

View the contents of the new table. When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.

REFERENCES
For more information about deleting duplicate records, click Microsoft Access Help on the Help menu, type Automatically delete duplicate records from a table. in the Office assistant or the Answer Wizard, and then click Search to view the topic.

This discussion is closed to new replies.