asked on

Sum query that gives amount of multiple instances of records

I have an Access table named MyTable with 10 fields. They are:

Field1
Field2
ORDER_ID_Field
Field3
Field4
Field5
Field6
Field7
Field8
AMOUNT_Field
Field10

The table contains some instances of duplicate and triplicate records which can be identified by the values in Field10. In other
words, it is the values in Field10 only that for my purposes determines whether a record is a duplicate or not.

The values in the ORDER_ID_Field are unique.

Not sure if this can be done in a single query but what I'm looking for is the dollar difference between the SUM of all records in the table and the SUM of all records in the table excluding multiple instances of the same record.

So, for example, just using three fields to illustrate, let's say ORDER_ID_Field, AMOUNT_Field, and Field10 contained the following:

ORDER_ID_Field AMOUNT_Field Field10
1 $100 Duplicate Record
2 $100 Duplicate Record
3 $50 Unique
4 $20 Triplicate Record
5 $20 Triplicate Record
6 $20 Triplicate Record

The expected results would be $140 (i.e. $310 total records - $170 single instance of records ). In other words, it is giving me the dollar amount of the multiple instances of records.

Thank you

Dale Fye

Would the amount field be the same in all of the duplicate records?

You might try:

SELECT Field10, Avg(Amount_Field) as AvgDups, Count(OrderID) as Freq
FROM yourTable
GROUP BY Field10
Having Count(OrderID) > 1

Open in new window

This should get you the Average amount for each of the values in Field10

You could then get your $140 value from this with:

SELECT SUM(Dups.[AvgDups] * (Dups.[Freq]-1)) as RedundantValues
FROM (
SELECT Field10, Avg(Amount_Field) as AvgDups, Count(OrderID) as Freq
FROM yourTable
GROUP BY Field10
Having Count(OrderID) > 1
) as Dups

Open in new window

John Tsioumpris

Shouldn't be $170 ?

Jenkins

ASKER

No. It's $140. I need to calculate the total amount of all records, which in the above case is $310. I then to subtract from that
all records excluding multiple occurences of them. So, in the above example, what should be included in the amount that gets subtracted from $310 is $100 for one of the Duplicate records, plus $50 for the Unique record, plus $20 for one of the Triplicate records. $310 - $170 = $140.

Mark Edwards

OK, since the Order ID is different for each record, a "duplicate" is defined by a repeated $ value, so what you say you are trying to do is just add the single, unique value for just those that have multiples - not including records that are not duplicated (unique) - so that means:
100 + 20 = 120
(Not including the second 100, the unique 50, and the second and third 20.)
Right? If not, please explain your math logic again? Why exclude the 50? why exclude only 1 of the 3 20s if you are "excluding multiple occurences"? It looks like you are saying one thing and doing something else...which is something called "not making sense", thus everyone's confusion.

Dale Fye

I wasn't confused.

He has a sum of all the amount fields, and wants to be able to determine what part of that sum is related to duplicate records, as defined by the value in [Field10].

So you define the duplicates from Field10 and subtract all of the values but 1 for each of those duplicates. So for "Duplicate Record" you subtract one instance of $100, for "Triplicate Record" you subtract 2 instances of $20, for a total of $140.

Mark Edwards

ahhhh…. I see, he wants the sum of the duplicate values, not the sum of the unique values. I was confused by "I then to subtract from that
all records excluding multiple occurences of them" - in other words, subtract everything BUT the duplicates leaving the sum of the duplicate values.... Never heard of doing that before. Always been the other way.

Thanks Dale for explaining it to me in simple terms.

ASKER CERTIFIED SOLUTION

Mark Edwards

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Mark,

More than one way to skin this cat!