Remove Duplicate Rec# and reimport with new Rec#(RESOLVED)

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Remove Duplicate Rec# and reimport with new Rec#(RESOLVED)

Post by Michael Abrams »

I have a table that I thought the RecordNumber (AutoNumber) was set to Index (Yes- No Duplicates)

It is set to Duplicates YES. What a mess.

I have 300+ duplicate record numbers with no specific criteria that I can separate them.

What is the most efficient way to keep one record of each set in the table (so the record number stays the same) and remove the other record temporarily?

Then I will have to update the setting to Duplicates NO.

Then I need to re-import the other record with its own record number.

**edit to add: It does not matter which record stays or goes. As long as the 2 of them are different rec#s

Is this even possible?

Thank you for any direction.
Michael
Last edited by Michael Abrams on 05 Nov 2014, 16:14, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by HansV »

Have you used the field to link to other tables?
If so, you have a real problem.
If not, I'd simply remove the field, then save the table design.
Next, add an AutoNumber field and make it the primary key.
When you save the table design, Access will assign unique values to the new field.
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by Michael Abrams »

Luckily - it is a standalone table.

Problem (one of these days I will write more than less) is that there are 3000 records that do have unique values and I must
keep their record# the same as it is now. It is just the 300 dups within the 3000 records that I need to handle.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by HansV »

You mention "with no specific criteria that I can separate them", but yet you want to keep one record of each duplicate. Seems contradictory...
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by Michael Abrams »

Each record has about 15 fields.

Dates, names, address etc.

Rec 123 could be John Jones 11/1/2014 etc
and the other
Rec 123 could be Mike Adams 03/1/1967 etc

Either record could stay as Rec#123. The other record would need a new record#

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by HansV »

Is the recordnumber really an AutoNumber field? If not, you could add an AutoNumber field, and use that to split the duplicates - keep the one with the minimum value for each record number.
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by Michael Abrams »

Unfortunately, it really is an Autonumber.

I need to think about this. We need to retain the record #s on all records except for the 300 dups -

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by Michael Abrams »

OK - I think I have an idea but need help for the last part.

I copied and pasted the database (structure only) and designated the Rec# as Primary Key - no duplicates.

I appended the original table into the new table and it imported the records (maintaining their orinial rec#) but did not import the 2nd record of the 300 record dups (good)

How can I now figure out how to identify and append the 300 records that did not import into the new database?

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by HansV »

Create a query based on the new old and the new table.
Join them on the Rec# field.
Add all fields from the old table to the query grid except for the Rec# field.
On one of the fields, say MyField, set a condition in the Criteria row:

<>[NewTable].[MyField]

See what this query returns. If the result is not what you want, you may need to use another field, or to add a similar condition on another field in the second Criteria row.
When it does what you want, you can change the query into an Append query that adds records to the new table. Since we didn't include the Rec# field, Access will automatically assign new values.
Best wishes,
Hans

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by Michael Abrams »

Thanks HansV - I will work on that today - that makes sense -

I will post back with result.

Michael

User avatar
Michael Abrams
4StarLounger
Posts: 573
Joined: 10 Feb 2010, 17:32

Re: Remove Duplicate Rec# and reimport with new Rec#

Post by Michael Abrams »

I actually couldn't wait ! :grin:

It worked perfectly.

Thank you so much HansV. Awesome as usual.

MIchael

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Duplicate Rec# and reimport with new Rec#(RESOLVE

Post by HansV »

Well, you came up with most of the solution yourself!
Best wishes,
Hans