baldmosher
New member
- Local time
- Today, 00:57
- Joined
- Oct 31, 2013
- Messages
- 9
*solved* Crosstab query problem: Sum is actually Sum*Count?
(Access 2010)
My source data (from Cognos) shows separate Charge Code entries on each row, Grouped By Shipment, Invoice, Account Number and Charge Code.
I first use an aggregate query that groups the Charge Codes amounts by Shipment (i.e. removes Invoices). This is working perfectly.
I then use a crosstab query that puts the Charge Code as column headers.
Shipment Reference is Row Heading (Group By)
Account Number is Row Heading (Group By)
Charge Type is Column Heading (Group By)
Total Revenue is Value (Sum)
My query appears to be producing very strange results in the Sum -- it seems to be multiplying the true sum of Total Revenue by some other amount. (Not entirely sure what, but changing to a Count instead of a Sum also shows incorrect counts so I wonder if it's counting up something completely random.)
NB: adding Group By Charge Code (not shown) shows the same sum results, albeit split onto separate rows for each Charge Code.
I found a workaround (using First instead of Sum on the Value field), which I think should work OK because of the way the source data is aggregated, but I'm not 100% convinced that won't cause any other issues.
Have I done something fundamentally silly here with my Crosstab query that's causing this to calculate sums incorrectly? It's almost as if it's completely ignoring Group By Shipment Ref -- does the order of Row Fields in the query matter?
(Access 2010)
My source data (from Cognos) shows separate Charge Code entries on each row, Grouped By Shipment, Invoice, Account Number and Charge Code.
I first use an aggregate query that groups the Charge Codes amounts by Shipment (i.e. removes Invoices). This is working perfectly.
I then use a crosstab query that puts the Charge Code as column headers.
Shipment Reference is Row Heading (Group By)
Account Number is Row Heading (Group By)
Charge Type is Column Heading (Group By)
Total Revenue is Value (Sum)
My query appears to be producing very strange results in the Sum -- it seems to be multiplying the true sum of Total Revenue by some other amount. (Not entirely sure what, but changing to a Count instead of a Sum also shows incorrect counts so I wonder if it's counting up something completely random.)
NB: adding Group By Charge Code (not shown) shows the same sum results, albeit split onto separate rows for each Charge Code.
I found a workaround (using First instead of Sum on the Value field), which I think should work OK because of the way the source data is aggregated, but I'm not 100% convinced that won't cause any other issues.
Have I done something fundamentally silly here with my Crosstab query that's causing this to calculate sums incorrectly? It's almost as if it's completely ignoring Group By Shipment Ref -- does the order of Row Fields in the query matter?
Last edited: