CODEWITHSUNDEEP
ms-accessms-access-2010
Grommit
Grommit

Reputation: 165

Sum down row across columns in Access CrossTable Query Report

I've got a Crosstable Query that correctly creates results that look like this:

              2010    2011   2012    2013
Company A      $5      $12    $20     $35
Company B      $50     $12     $2     $90
Company C      $100    $1     $87     $54

All good.

If I put this in a report, it is easy enough to get a total across the bottom by year.

               $155     $25    $99     $179

I'd like to also have the report sum across the row, to give me something that looks like:

                                           Total
                                            $72 
                                            $154
                                            $242

I can't figure out how to pull this off. Is it possible? Should I do it in the query, report, or both?

Thanks.

Upvotes: 1

Views: 15863

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

A basic Crosstab Query will look like this...

TRANSFORM Sum(SalesSummary.[Sales]) AS SumOfSales
SELECT SalesSummary.[Item]
FROM SalesSummary
GROUP BY SalesSummary.[Item]
PIVOT SalesSummary.[FiscalYear];

...producing results like this

Item      2011  2012
--------  ----  ----
bicycles  1000  2600
ham        100   260

To include row sums, add a Sum() column to the SELECT clause as well...

TRANSFORM Sum(SalesSummary.[Sales]) AS SumOfSales
SELECT SalesSummary.[Item], Sum(SalesSummary.[Sales]) AS [Total Of Sales]
FROM SalesSummary
GROUP BY SalesSummary.[Item]
PIVOT SalesSummary.[FiscalYear];

...producing

Item      Total Of Sales  2011  2012
--------  --------------  ----  ----
bicycles            3600  1000  2600
ham                  360   100   260

That's what the Crosstab Query Wizard does when you select the "Yes, include row sums." option:

xtab.png

Upvotes: 2

Related Questions