Access Query Question

[gwilks98]

We're trying to create primary keys with a table of 6,000 records. However, it says that duplicates would occur if we do so.

Anyone know how to create a query to show the duplicates? I only know how to create a query to remove them.

[Jcranmer]

Give this a try:


http://www.techonthenet.com/access/queries/find_dups.htm

[Sesshomaru]

We're trying to create primary keys with a table of 6,000 records. However, it says that duplicates would occur if we do so.

Anyone know how to create a query to show the duplicates? I only know how to create a query to remove them.

There's a wizard that will create that query for you

[Agent Plissken]

To get to the wizard, go to the Query area. Click NEW, select Find Duplicate Query, select the column you wish to check (select only this column which you would like to be key) click next, leave this blank, then finish.
it will have the entry followed by how many times it occurs in the list.

[mcs328]

How many columns are in this table? Can you create another column, designate that as primary key (autonumber) and be on your merry way? Or will this table link up with other tables using existing columns as the combination primary key?

[gwilks98]

How many columns are in this table? Can you create another column, designate that as primary key (autonumber) and be on your merry way? Or will this table link up with other tables using existing columns as the combination primary key?

Can't do that. This dbase has to be standardized with the other 173 databases throughout the company. A change to one needs to happen on all.

Really, don't ask why. Another not-so-smart decision made before my time.

I'll try all of y'all suggestions and figure it out. Thanks for the help guys.

[DarkFury]

One stupid question here...

If you are gonna created a "primary key" field... then doesn't that field have to have a UNIQUE number in it be a "primary key"? :shrug:

If you have duplicates in the database table... then what good will that do ya if you try to make that the primary key field? :shrug:

[Agent Plissken]

He most likely has duplicates that can be removed because they are accidental. Example: Part number 342e3l7e9c2t8b0u6s3h04 (which will become the unique key) was entered in twice. One entry may have out of date price info or something, so will be terminated with extreme prejudice.
Anyway, post again if you cant figure it out and I will help you in more detail from my office (ok cube) on Tuesday.

[gwilks98]

The problem was the dbase lost it's primary keys for all 40 of it's crucial tables. Most were recovered ok. But one table has employee punches and some punches got duped.
Now we can't re-establish the primary composite key until we remove the dupes.

I'm still working on tweaking that query. I got the results, but according to it, there's 250 dupes. I'm looking for a way to streamline a bit.

[mcs328]

Hmm...I would be hesitant to delete records if there are duplicate primary keys (oxymoron) and the other columns contain different data sets. How would you know which records with the same primary key columns has the most up to date data? Well I guess after you find those suspect records someone makes a judgement call to delete the duplicate or something.

I guess this is a band-aid approach until someone bunkers down and decides to build from the ground up. The write a script to transfer the data from the original tables to the database. Good luck to ya!!

[zerogeeR6]

Try creating a primary key using two fields. i.e. the number that keeps repeating plus something else unique like a last name etc.

[gwilks98]

Try creating a primary key using two fields. i.e. the number that keeps repeating plus something else unique like a last name etc.


that's the problem. The entire record is duped. Not just one field in that record. There's no way to distinguish between the two records. One has to be deleted before a primary key can be established. (And adding another field is not possible).

:-(

[SmokeyDP]

I ran into this problem with a table with over 40K rows. Just create a new query. Add in all of your parameters separately(do not use the *). Turn on the group by. If the records are indeed identical then it will take 2+ of the same and only return one row. Then set the query to output a new table.

Then see if you can create a primary key on that same table.

If you still have a few then create a query to return the names of all the keys where the count is > 1(meaning there is at least one dup) and go through them manually.

[mcs328]

so did you get it figured out yet? I like Smokeys suggestions.

[SmokeyDP]

You can also use a query to create a table of ONLY records that dont have a dup keyfield. They use another query to copy of everything that is a dupe.

You can then start using the first query in production while you pay a grunt to manually copy over the dup data so you atleast have something to work with.