row/column totals in crosstab query

  • Thread starter Thread starter Marge
  • Start date Start date
M

Marge

I've been able to get some of my desired results in a crosstab query, but
getting totals has stumped me. Here's my data....

Color shirt pant dress totals
red 114 162 276 ?
blue 14 139 153 ?
green 153 314 467 ?
total ? ? ? ?

The "?" represent the totals that I'd like Access to cough up, but I can't
figure out how to ask for them.

Any help is greatly appreciated.
 
Open the query in design view. Observe the field name used for the
Crosstab:Value and add that same field in the Field: Row. Click in the box
and move cursor to left of the first letter.
Type Totals:
In the Total: row select Sum and in the Crosstab: select Row
Heading.

This will give a total for each color as your crosstab is presently designed.

It is a whole other ball game to get the total for the articles across the
bottom.

I assume your data for the crosstab query is like this --
Article Color Quanity
Pant Red 23
Pant Blue 54
Shirt Green 14

You need to create a union query like this to feed the crosstab --
SELECT Color, Article, Sum([Quanity]) AS Qty
FROM Inventory
GROUP BY Color, Article
UNION SELECT "Total" AS Color , Article, Sum([Quanity]) AS Qty
FROM Inventory
GROUP BY Color, Article;
 
Back
Top