Updating All Duplicate Records

[bcquadrant]

Hi there,

I hope someone can help me with my problem,

I have a database with about on average 30,000 records in, and most of them are duplicates, however the duplicates are required for the work we do, as each represents a different payment, but only 1 action is required.

What I need is a button than you can press and it will go through the entire recordset and will update all the duplicate records and then at the end it will show the amount of unique duplicates updated.

Heres an example of the records in data view:

MPAN | Advisor | Date | Action
--------------------------------------------------------
123456789010 | Bc | 24/09/07 | Cleared rec
123456789010
123456789010
554444444444
554444444444 | TH | 04/09/07 | Unworked

(" | " indicates field)

What I need is for it to start with the first MPAN, update the fields, then move onto the next unique mpan and do the same, and so on.

Thank you,

Brendan

[cyberdwarf]

Brendan

1. make sure you have a unique (autonumber?) field in your table;
2. create a separate table to hold the unique MPAN numbers (useful for ensuring uniqueness!);

Assuming your tables are called "Main" and "Child", you can now use some code like:-

Update Child Set Child.Action='???', Child.Date=Today() Inner Join Main On Main.MPAN=Child.MPAN

HTH

Steve

[FishVal]

Hi, Brendan.

I strongly suggest you to read the article about
Database Normalisation and Table structures

And, plz, clarify what do you mean saying

it will go through the entire recordset and will update all the duplicate records

.

[bcquadrant]

Hi,

Basically, I pretty much want something to run through the 30,000 records, identify an MPAN and update its duplicates, then move onto the next MPAN and do the same.

I thought about using more than 1 table to do this, such as a "Child" and "Main" as Steve said, but we have to do new imports everyday and that again causes more duplicates. So one way or another, since we need them, it needs something to deal with duplicates :)

[cyberdwarf]

My point still stands. You can still import as usual into the Child table, then check for duplicates in the Main table; if not found, then insert a new Main record, finally running an update query[font=Verdana][size=2], as suggested[/size][/font]

[font=Verdana][size=2]Steve[/size][/font]

[FishVal]

I agree with Steve. Some import "difficulti es" should not be a reason for breaking database normalization.
Take a look at this thread
Mass Import w/Multiple Tables

And you didn't clarified yet what type of update you wish to perform.

• do you want to update field(s) with constant value ?
• do you want to update field(s) with matching data from another dataset ?
• do you mean something else ?

[bcquadrant]

I really appreciate your replies, and thank you.

I guess im not really explaining myself that well am i, heheh.

I am currently in the process of converting an excel spreadsheet (which we currently work out off) into an access db, unfortunately the only thing holding me back is the duplicates. Daily imports from an external source means more duplicate records, and updating those records is a required step in the import process, the fields that need updating are the Advisor, Action, and date worked cells.

Creating multiple tables, such as one for all duplicates, and one for non duplicates (main & child) isnt going to be that helpful unless theres a way to update the duplicates first in the morning.

The duplicates occur.. for example if I worked an account yesterday, and it only had 1 record, another payment from that account number might come in on the imports and the same account would then appear again the next day, so any work that was worked previously and has new duplicates imported need to be updated. But since theres so many records, theres no way any person could scan through all those records to manually update them all.

Ideally the thing I am looking for is for it to locate and update the records with the information filled in on a previous duplicate to all those that are blank, and count how many unique mpan's it has updated, and then it would be easy enough to do database normalisation.


Before update:
MPAN | Advisor | Action | Date
1234567890 | Brendan | Worked | 24/09/07 <old
1234567890 <new
4321093123 <new
4321093123 | Brendan | Unworked | 23/09/07 <old
2233112233 < new
2233112233 < old

After update:
1234567890 | Brendan | Worked | 24/09/07 <old
1234567890 | Brendan | Worked | 24/09/07 <new updated (counted)
4321093123 | Brendan | Unworked | 23/09/07 <new updated (counted)
4321093123 | Brendan | Unworked | 23/09/07 <old
2233112233 < new (not counted, as not worked)
2233112233 < old

Unfortunately I have no idea how to do this.

Thank you,
-Brendan

[FishVal]

Hi, Brendan.

Please post the MetaData for your table. Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time