Merge duplicates

Topic

New Reply

Hi, I have almost finished normalising data from an Excel sheet. I have for example, clients, who can have many accounts, and those accounts can have many transactions..

I have duplicate entries at client level though. I have isolated them in a table but I am now struggling to break them down appropriately. So I have eg Darren, anytown with ID of 1 then one account listed with it’s many transactions. Then have the same Darren but with an ID of 2 with 1 account listed and it’s many transactions. I am looking to consolidate, so dDrren 1 and 2 are merged to perhaps Darren, with account 1 below him with it’s transactions and account 2 below him with its transactions. Is there a way to do this using a combination of update and append queries, dropping and adding new PKs etc?

Thanks, Darren.

Reply | Quote

Replies

Have you run a duplicates query against the table to identify the actual number of duplicates? If so, pick one of the duplicates and run update queries for any accounts that belong to duplicates other than the one you picked, and set their link to the client table to the one you have chosen to keep. Then delete the duplicate clients you chose not to keep. It’s a tedious process at best – and depending on the number of records you might want to do some of it manually and some with queries. But it can be done.

Reply | Quote

Have you run a duplicates query against the table to identify the actual number of duplicates? If so, pick one of the duplicates and run update queries for any accounts that belong to duplicates other than the one you picked, and set their link to the client table to the one you have chosen to keep. Then delete the duplicate clients you chose not to keep. It’s a tedious process at best – and depending on the number of records you might want to do some of it manually and some with queries. But it can be done.

Thanks, WendllB? This works ok, is there no way perhaps with SQL that we can say update ID to the row below or something like?

Regards, Darren.

Reply | Quote

Thanks, WendllB? This works ok, is there no way perhaps with SQL that we can say update ID to the row below or something like?

Regards, Darren.

Unfortunately, using SQL there isn’t any easy way to refer to a next or previous row. To do that sort of thing you would have to resort to Visual Basic and either DAO or ADO to update the recordsets you are working on.

Reply | Quote