deleting duplicate records (1 Viewer)

clean32 · Jun 28, 2016

ok i have a table, 7 fields.
because this data is past append from an different report. some times with the date range of this other report we end up with duplicate records.

now in access 1 days i would have made a query with a sort and count, first record. opened that in a form. used that for as criteria to open a delete query ( first record). and run that ver a macro that speed though the records in the form until none were left = null.

it is not working now.

i discovered a duplicates wizard, that works but can not delete. atchaching that back gives me all the duplicates co can not delete there ether.

so summery. i have duplicate records in a table. 2 identical records in all fields.

how do i delete one of these duplicates?

sneuberg · Jun 28, 2016

Check out the Delete duplicate records section of http://www.allenbrowne.com/subquery-01.html#TopN . If you have problems setting this I will set it up for you if you zip a copy of your database and upload it. You could strip it down to just the table in question with a few representative records. It might be faster to create a new database and import this table than stripping down a copy.

clean32 · Jun 28, 2016

there we go, and thanks

sneuberg · Jun 28, 2016

It appears that a record is a duplicate if all of the field have equal values. Is this true or is there a subset of fields that would define a duplicate?

clean32 · Jun 28, 2016

sneuberg said:
It appears that a record is a duplicate if all of the field have equal values. Is this true or is there a subset of fields that would define a duplicate?

all fields
because the data is pasted from another computer system that i do not have access to. this pasting is also done by other people who at times can not understand the difference between a fortnightly schedule and monthly dates.

there are no other fields, but if need be we could invent one

sneuberg · Jun 28, 2016

Thanks. I think it will be easiest to do this is in code. I'll get it to you soon. I hope.

sneuberg · Jun 28, 2016

You can find the code in the module Delete Duplicates in the attached database. You can run this code by calling the subroutine DeleteDuplicates. So if this is going to be a recurring task you can copy the code in the module to a form module and put DeleteDuplicate in the code for a command button.

This code is generic and will work with any table but depends on a query named qrySorted. That query needs to have all of the fields in the table and they all need to be sorted in ascending order. This is to ensure the duplicate records are adjacent for the code. The code simple scans the recordset of qrySorted from top to bottom and when it finds two adjacent records with all of the fields have equal values it deletes one of them.

I made a copy of the SA report append in case you want to test the code a couple of times.

clean32 · Jun 28, 2016

great and thanks

i read it understand it not that i could do it.

so sill question, i tried to call your module from a macro,

run code "Delete Duplicates" didn't work

sorry
what am i doing wrong?

sneuberg · Jun 28, 2016

You can't run subroutines from a macro. Just one of the many reason why I hate them. I never use them and I tend to forget that other people do.

In the attached database I change the DeleteDuplicates subroutine to a function. Also included is a macro that calls DeleteDuplicates and a form with a button that runs the macro.

Galaxiom · Jun 28, 2016

Wouldn't it be better to avoid inserting the duplicates into the table in the first place?

This can be done using a Update query with outer joins from fields in the source table to fields in the destination tables and Is Null criteria on all the destination fields.

Moreover, duplicate records should be prevented from ever being able to be inserted by putting a Composite No Duplicates Index on the seven fields.

sneuberg · Jun 28, 2016

I created a table with a Composite No Duplicates Index on the seven fields as Galaxiom suggested and appended about 140,000 records to it as I wanted to see if that would just take to long but it doesn't. You get a warning about duplicate records but if you click on yes you end up with a table with just the unique records. This is a lot simpler than the code I wrote. Wish I would have though of it.

clean32 · Jun 29, 2016

ok got it

a table to paste into.

append query with a new field, ie f1&f2&f3 etc

same om table being pasted to but this field is ID no duplicates

ok simple just turn off warnings

good enough for the blonds in the office

Galaxiom · Jun 29, 2016

The down side of turning off Warnings is that any other reasons for rejecting records (eg wrong datatype or overflow) will be missed.

Do explore the option of an Update query with a outer joins from the source to destination fields and Is Null criteria. This will reject duplicates without throwing errors.

sneuberg · Jun 29, 2016

I agree with Galaxiom about using an outer join but just so you know you don't have to add an additional field to have a composite index. Just create one from all of the fields as shown in the attached screen shot.

sneuberg · Jun 29, 2016

Galaxiom said:
Do explore the option of an Update query with a outer joins from the source to destination fields and Is Null criteria. This will reject duplicates without throwing errors.

On the other hand this may not help as the OP stated

all fields
because the data is pasted from another computer system that i do not have access to. this pasting is also done by other people who at times can not understand the difference between a fortnightly schedule and monthly dates.

If he gets the data with the duplicates already present I don't think this outer join method will help. Maybe the code I provided is the best route after all.

Galaxiom · Jun 29, 2016

sneuberg said:
If he gets the data with the duplicates already present I don't think this outer join method will help. .

In that case run a SELECT DISTINCT query on the input data to supress duplicates, then join that query the as the source instead of the table.

BTW I said Update query instead of insert query in my earlier post. You can do it with either but the Insert is obviously easier.

Update works to both update existing records (matched on the joins) and insert new ones.

clean32 · Jun 29, 2016

sneuberg said:
I agree with Galaxiom about using an outer join but just so you know you don't have to add an additional field to have a composite index. Just create one from all of the fields as shown in the attached screen shot.

ok did not get that

clean32 · Jun 29, 2016

Galaxiom said:
In that case run a SELECT DISTINCT query on the input data to supress duplicates, then join that query the as the source instead of the table.

BTW I said Update query instead of insert query in my earlier post. You can do it with either but the Insert is obviously easier.

Update works to both update existing records (matched on the joins) and insert new ones.

jes that will work. where there are no matching records is null. then append query. the update i think will not work

Galaxiom · Jun 29, 2016

clean32 said:
jes that will work. where there are no matching records is null. then append query. the update i think will not work

Update can work too but it is unnecessarily complicated when you don't actually need to update existing records.

Many developers don't realise that an Update query with an outer join can insert records as well as updating existing records. Basically it is updating the new record.

clean32 · Jun 29, 2016

Galaxiom said:
Update can work too but it is unnecessarily complicated when you don't actually need to update existing records.

Many developers don't realise that an Update query with an outer join can insert records as well as updating existing records. Basically it is updating the new record.

yep got that working fine, simple.
it will stop duplicates getting in

But i already have duplicates. so next step.

i have done this in the past, that was in access 1 days

form with filter, ascending and count then <1, that gives all the duplicates.

another form with criteria based on the above form, this gives say two records IE the duplicates for each duplicate record in the first form.

a macro, open first form, open second form, go to record, first record. the do menuitem DELETE.

close or refresh the forms and do again. criteria for that macro iif not equal null, in the first form.

now that has always worked well for me. BUT now i have access 2007. first problem friggen ribbons sheesh who's bright marketing type of a silly idea was that. and now there is NO DOMENUITEM. WTF?

so im stumped. what next?

deleting duplicate records (1 Viewer)

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

AWF VIP

Attachments

Registered User.

AWF VIP

Attachments

Super Moderator

AWF VIP

Registered User.

Super Moderator

AWF VIP

Attachments

AWF VIP

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread