Yet another duplicate record dilemma

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dk_cub - 13 Jan 2008 21:08 GMT

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.

Reply to this Message

Arvin Meyer [MVP] - 13 Jan 2008 21:40 GMT

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.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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

Reply to this Message

dk_cub - 13 Jan 2008 23:01 GMT

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

> Use the Find duplicates wizard, the build an Update query and either add to
> the field:
[quoted text clipped - 14 lines]
> > and
> > update the record field with the new total.

Reply to this Message

John W. Vinson - 14 Jan 2008 01:07 GMT

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

Reply to this Message

Arvin Meyer [MVP] - 14 Jan 2008 01:26 GMT

Yes I went too fast, thanks for the catch John.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub
> <dkcub@discussions.microsoft.com>
[quoted text clipped - 15 lines]
>
> John W. Vinson [MVP]

Reply to this Message

dk_cub - 14 Jan 2008 06:55 GMT

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!

> Yes I went too fast, thanks for the catch John.
> > On Sun, 13 Jan 2008 15:01:00 -0800, dk_cub
[quoted text clipped - 16 lines]
> >
> > John W. Vinson [MVP]

Reply to this Message

Arvin Meyer [MVP] - 14 Jan 2008 15:21 GMT

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;

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Sorry, it appears to add all the "MyField" values. I think I'll need to
> use
[quoted text clipped - 38 lines]
>> >
>> > John W. Vinson [MVP]

Reply to this Message

dk_cub - 14 Jan 2008 19:59 GMT

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

Thanks again,
Doug

> No, a second query, based on the first will do the job:
>
[quoted text clipped - 43 lines]
> >> >
> >> > John W. Vinson [MVP]

Reply to this Message