Crosstab query problem: Sum is actually Sum*Count? (1 Viewer)

baldmosher

New member
Local time
Today, 20:49
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?
 
Last edited:

baldmosher

New member
Local time
Today, 20:49
Joined
Oct 31, 2013
Messages
9
Well, I've ruled out the order of Row Headings... makes no difference, still totals incorrectly
 

CazB

Registered User.
Local time
Today, 20:49
Joined
Jul 17, 2013
Messages
309
can you show / upload a sample - base data and 'wrong' totals?
 

baldmosher

New member
Local time
Today, 20:49
Joined
Oct 31, 2013
Messages
9
Tricky... the base data is about 800MB :)

I'll try screenshots, need to find a reliable upload service from work..... proxy annoyance... hang fire a few mins
 

baldmosher

New member
Local time
Today, 20:49
Joined
Oct 31, 2013
Messages
9
I couldn't find a suitable upload site, so sorry, I've cross-posted to another forum that allows attachments, you might have to register there to view the attachments.

www
.utteraccess.com/
forum/Crosstab-Sums-Incorrect-t2012120.html
 
Last edited:

baldmosher

New member
Local time
Today, 20:49
Joined
Oct 31, 2013
Messages
9
now solved, see other thread linked above

It was caused by a table relationship, multiplying the sum result for each Category by the number of times that Category appeared in the lookup table
 

Users who are viewing this thread

Top Bottom