Deleting duplicate records/same table

[Stacie2410]

I'm having trouble trying to delete duplicate records in a same table.

What I've got, is one table, that has around 12,000 records. What it should
have, is around 2,000 records. What has happened, is that the original 2,000
records somehow were duplicated 5 times each, and these duplicates need to be
deleted. I do not have a primary key set at the moment, but I do have a
field called "Record Number" that needs to be unique.

I've ran some other queries that will get rid of duplicates, but what it
did, was delete anything that was a duplicate of anything else. For instance,
if there were 5 of one record, it would delete all 5, rather than deleting 4,
and leaving 1. There's no unique factor about each row, such as a more
current date, etc, they are all identical, so I don't know how to tell it to
keep one, but trash the rest.

Any help you can provide is GREATLY appreciated. I'm pulling out my hair
trying to fix this for the past 5 days.

Thank you!

 

[Michel Walsh]

Don't delete, append to a new table with an index not allowing duplicated
values. When you will append the data, some records won't be added because
they would create a dup. This will be reported as a possible error, but that
is exactly what you want.


Hoping it may help,
Vanderghast, Access MVP

 

[Stacie2410]

That's actually what I tried, but the results were only the records that
never had a duplicate, which was about 20 records. The other 2000+ records
didn't come over. Basically, anything that ever had a duplicate of it, didn't
make it.

Example: There were 5 of each records. When I did the append query, it
didn't bring over any of those 5, and I wanted it to bring over 1, and leave
the other 4.

I don't know if this makes sense, I'm probably not explaining it very well,
my knowledge of Access isn't as extensive as I'd like it to be.

 

[Michel Walsh]

You are using Jet, not a table linked to MS SQL Server? If you use Jet, you
should end up with one record out of the 5-dups.


An alternative is to make a group by query, then, make a table out of it. In
the query designer, bring your table. Click on the summation button, the one
with the capital Sigma, a rotated M. A new line, total, appears in the grid.
Bring the field that should not be duplicated in the grid, keep the GROUP
BY. Bring all other fields, change the GROUP BY to LAST. Run the query to
see that you get what you want. Save the query. Create a second query, a
'MAKE TABLE' query, based on the query you just saved. Once your new table
is created, add the index not allowing duplicated values, so the problem
won't re-occur.




Hoping it may help,
Vanderghast, Access MVP

 

[Stacie2410]

Ok, I've done this, and it works, but I have one problem. The table I used
was called tblMaster. The new table without the duplicates is called
tblUniqueRecords. If I delete the tblMaster and rename tblUniqueRecords to
now be tblMaster, is it going to mess up the rest of the database that is
tied to tblMaster? I did this, and tried to run a switchboard item that runs
a macro and a query but it gives me a "Enter Parameter Box" that says
"tbl.Master .mPreparedBy". Prepared By is the type of query I was trying to
run (based off of someone's name).

Did I do something wrong?

 

[Michel Walsh]

1- Make a backup.

2- You make the new table, tblUniqueRecords

3- You delete all records from tblMaster.

4- You append the records from tblUniqueRecords into tye now empty
tblMaster

5- You drop the table tblUniqueRecords.

6- Add the index not allowing dup in tblMaster


Step 3 may delete records from other tables if you have defined CASCADE
DELETE relations, which is what we don't want, in this case. That is why
step 1 is important.




Vanderghast, Access MVP

 

[Stacie2410]

When I create the new table "tblUniqueRecords", it's naming all my fields the
same, except with "Lastof" infront of it. For instance instead of
"mPreparedBy", it's now "LastofmPreparedBy". For this reason, it won't
append the tblUniqueRecords fields into the fields on tblMaster. Is there
any way to avoid the renaming?

 

[Michel Walsh]

Yes, you can rename them after the table is created (in table design view),
removing the prefix "last". It could bring some confusion if you do it
before, since the same name will be used to describe the new result, and the
original value (eventually skipped out, but eventually kept too).


Vanderghast, Access MVP