CrossTab Query Column Total

Flinn, Randal J 281 Reputation points
2022-04-14T18:33:13.107+00:00

I am running a report from a crosstab query. Is there a way to total the column amounts in the report?

Example:
FY2020 FY2021 FY2022
Location 1 5 4 3
Location 2 6 7 8
Location 3 5 3 9
Total 16 14 20

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
845 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ken Sheridan 2,751 Reputation points
    2022-04-14T23:08:34.85+00:00

    Just as you would with a conventional report, you can place unbound text boxes in the report footer, each with an expression calling the SUM operator as its ControlSource property, e.g. =SUM([FY2020]) for the first column, and so on for the other columns. Alternatively you can aggregate the values by means of another crosstab query grouped by TRUE (or any constant for that matter) to return the grand totals. The following is an example:

    TRANSFORM NZ(SUM(Amount),0)
    SELECT SUM(Amount*IIF(salestype IN ("Cash","Credit"),1,0)) AS TotalSales
    FROM Sales
    GROUP BY TRUE
    PIVOT salestype IN ("Cash","Complimentary","Credit");

    You can then include a subreport based on the query in the original report.


0 additional answers

Sort by: Most helpful