How to create an Access crosstab query with totals for the columns AND the rows?

Question

I want my query result to look like this:

          Person1 Person2 Person3 Person4    Total 
Status1         2       4       7       3      16
Status2         0       1       0       3      4
Status3         0       0       0       0      0
Status4         0       1       3       0      4
Total           2       6       10      6      24

I'm able to get everything except that bottom row with:

TRANSFORM Count(personName) 
SELECT status, Count(status) AS Total
FROM table1 
GROUP BY status
PIVOT personName

I found something about using a UNION to tack on the last row, but I can't seem to quite get that right. Seems like this should be a common activity.

Answer 1

You'd basically have to run your query twice - once to get the data and then a second time to provide the aggregates. If you're set on doing this, make the first query to return data its own object. Then make another query to aggregate the first one another object. Create a final third query object to combine the two using a UNION as you mentioned.

Although I have to say I don't really recommend this. It sounds like you're trying to force the SQL to generate something that's really presentational information (i.e. it doesn't belong in the same dataset).

Answer 2

There is actually a simple solution to this issue. Once you have designed your crosstab query, go into design mode within the query and select "Totals" in the Records section on the Home tab. Then you can select the Sum or Count etc....

Here is a link that gives steps: http://office.microsoft.com/en-us/access-help/display-column-totals-in-a-datasheet-HA001233062.aspx