Yet another duplicate record dilemma

[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.

 

[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.

 

[dk_cub]

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

 

[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]

 

[Arvin Meyer [MVP]]

Yes I went too fast, thanks for the catch John.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[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!

 

[Arvin Meyer [MVP]]

No, a second query, based on the first will do the job:

SELECT tblMyData.Field1, Sum(tblMyData.Field2) AS SumOfField2
FROM tblMyData
GROUP BY tblMyData.Field1;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[dk_cub]

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

Thanks again,
Doug