Yet another duplicate record dilemma


D

dk_cub

I have a table with records where one field are duplicates. I'm able to
query to find duplicates and delete them, however what I need to do is find
the duplicates, produce a total from another field, delete the duplicates and
update the record field with the new total.
 
Ad

Advertisements

A

Arvin Meyer [MVP]

Use the Find duplicates wizard, the build an Update query and either add to
the field:

Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField +
Query1.MyField

or just update it:

Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField =
Query1.MyField

Then delete the duplicate data.
 
D

dk_cub

Thanks Arvin, however, I get an error when saving the update query - "Join
expression not supported". Hmmm...
 
J

John W. Vinson

Thanks Arvin, however, I get an error when saving the update query - "Join
expression not supported". Hmmm...

I think Arvin just was typing a bit too fast... should be

Update MyTable Inner Join Query1.ID
On MyTable.ID = Query1.ID
Set MyTable.MyField = MyTable.MyField +
Query1.MyField;

but I'm not certain it's going to work if Query1 has multiple records or a
group by.





John W. Vinson [MVP]
 
D

dk_cub

Sorry, it appears to add all the "MyField" values. I think I'll need to use
VBA to loop through the records and sum the desired field values...
What I've got:

Field1 Field2
ab1 2
ab2 5
ba1 1
ab1 2
ba1 1

Desired result:

Field1 Field2
ab1 4
ab2 5
ba1 2

Thanks for all the patience!
 
Ad

Advertisements

Ad

Advertisements

D

dk_cub

Excellent! Works great! Thank You! Your assistance and patience is very
much appreciated.

Thanks again,
Doug
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top