Sum of Duplicate Records Based on Currency Value 1

[larrydavid]

Hello,

I have a duplicate query which is giving me the records I need, each record has one column (AMOUNT) with a currency value. I've been researching this and trying different approaches, but am stumped. Can someone please show me how I can perform an aggregate SUM on the one field and combine the 2 (or in some cases 3 or more) records into one record, with the SUM total of all currency values for all duplicate records in one remaining record. It is OK to lose the data in the other records, so I've been trying to SUM on the last occurrence of the duplicate. Any help would be greatly appreciated.

Thanks,
Larry

 

[Golom]

Can you provide a bit more detail?

I'm unclear about exactly what (i.e. which field or fields) are being duplicated. If you can provide some sample data and the result that you are expecting then we may be able to suggest a solution.

 

[larrydavid]

OK, I guess a picture is worth a thousand words. Here is an example of a dup record. In this case, what makes this a dup record is the 1st, 2nd, and 3rd values are the same (kind of a composite key if you will). So, here is a sample dup set:

Value 1 Value2 Value3 Value4 Value5
12345 001 1111 9876 $10.00
12345 001 1111 8746 $10.00
12345 001 1111 7586 $34.00

So, for the records above, I need to rollup the records to one record which has the SUM of Value5 to the last record:

Value 1 Value2 Value3 Value4 Value5
12345 001 1111 7586 $54.00

I found that duplicate queries usually need a sub query, then you can do your SUM query on top of that, where you can select LAST and SUM... but it's not exactly working for me like this in this case for some reason, or perhaps I'm not sure how to implement this. Any example or suggestion would be welcome.

Thanks,
Larry

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

From the "picture is worth a thousand words" you have provided, this will work. If your picture is wrong then please provide a more accurate picture.

SELECT [Value 1], Value2, Value3, Min(Value4) as Min4, Sum(Value5) As Sum5
FROM [No Name Given]
GROUP BY [Value 1], Value2, Value3;

Duane
Hook'D on Access
MS Access MVP

 

[larrydavid]

Hello,

Thank you Mr. Hookum, I am a big fan of your work. Thank you so much for responding. Almost there. Sorry, one thing I failed to mention was that this would need to be applied to groupings as follows:

Value 1 Value2 Value3 Value4 Value5
12345 001 1111 9876 $10.00
12345 001 1111 8746 $10.00
12345 001 1111 7586 $34.00
12348 002 4444 7465 $39.56
12348 002 4444 8837 $39.52
12348 002 4444 9837 $39.52

Also, the Value4 is not in any kind of sequential (asc or desc) order.

So, essentially, this is a running total for each grouping, with the previous records in each group being discarded and the remaining record displaying the sum.

Sorry, this has been a challenge for me to define.

Thanks,
Larry

 

[dhookom]

Which fields are in the grouping? I assume it is the first three fields are your grouping. If this is the case which value of the 4th field do you want to include? Don't say the "Last" since there is no last in tables. You must be able to mix up all the records and be able to identify the one you want to pick.

Duane
Hook'D on Access
MS Access MVP

 

[larrydavid]

Yes sir, the first three fields are the grouping. The value of the 4th field does not matter. I was trying to get the last(most recently entered record) in each grouping to display as the record with the sum of Value5.

Thanks,
Larry

 

[dhookom]

There is no LAST or MOST RECENTLY ENTERED without some field value(s) that determine the most recent. You can use LAST if you are satisfied with some random value.

Duane
Hook'D on Access
MS Access MVP

 

[larrydavid]

Thank you Duane and everyone for your valuable input, I really appreciate it.
Best,
Larry

 

[larrydavid]

Oh By the By, here is the solutino that seems to work for me:

Query2: Totals
SELECT Count(Query1.key) AS NbrofDuplications, Last(Query1.Value4) AS LastOccurrence, Sum(Query1.Value5) AS TotalSum
FROM Query1;

Query1: Duplicates
SELECT [Value1] & [Value2] & [Value3] AS [key], Value4, Value5
FROM Table1
WHERE ((([Value1] & [Value2] & [Value3])
In (SELECT[Value1] & [Value2] & [Value3] FROM [Table1] As Tmp
GROUP BY[Value1] & [Value2] & [Value3] HAVING Count(*)>1
)));

Regards,
Larry

 

[PHV]

Why not simply this ?

SELECT Count(*) AS NbrofDuplications, Last(Value4) AS LastOccurrence, Sum(Value5) AS TotalSum
FROM Table1
GROUP BY Value1, Value2, Value3
HAVING Count(*) > 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886