Merge Duplicate Records

[Cobb]

I have an Inventory Database where I have imported Purchase History records from our ERP system so I can use this historical data for tendering, however we have many records where the Master Data was found to be duplicated (Record X = Record Y) and I now want to consolidate this data into the one record - can the following be done easily via VBA Macro/Query..?
1. Nominate Primary Key 1 (Deleted Item Material No)
2. Nominate Primary Key 2 (Retained Item Material No)
3. Query then combines the value/s from specified Field/s from #1 and adds these to the values of those same fields for #2
4. Then deletes the record entirely for #1

I have over 500 of these Duplications so far and merging these manually is just too onerous. There has to be an easier way to automate this and my VBA is not that good yet.

Any assistance is appreciated here

 

[arnelgp]

as i can see it you need to have 3 queries to perform.

1. item #3 needs to update records from table1 to table2 only on same PK:

update table2 inner join table1 on table2.pk=table1.pk set table2.field1=table1.field1, table2.field2=table1.field2, .....;

2. then delete those record from table1:

delete * from table1 where pkField in (select pkField from table2);

3. update table1 with new records from table2:

update table1 right join table2 on table1.pk=table2.pk set table1.field1=table2.field1, table1.field2=table2.field2, ...;

 

[Cronk]

"can the following be done easily via VBA?"
Maybe

Change that to
"can the following be done via VBA"
Probably but depends

Can you provide the names/types of the fields currently in the table with the duplicate data and some examples of the data?