Total Rows in crosstab query

[Gene]

Does anyone know if it is possible to have a column total in a crosstab query?

Here's my setup:
For RowHeadings I have:
UnitNo
UnitName
RegionNo

For ColumnHeading I have:
Quarter

I'm using Amount as a sum value...

The query looks like this:

UnitNo UnitName RegionNo TotalofAmt Q1 Q2 Q3
166 NewYork 100 500.00 125.00 250.00 125.00
275 Virginia 115 75.00 25.00 25.00 25.00

I not only need a total of all quarters (which I get from the TotalofAmt column, but I would like to total each quarter. For example, I would like to know that Q1 totals 150.00

Can this be done in a crosstab query, or should I be looking at an alternative method.

Thanks for any help someone can provide,
Gene

 

[Duane Hookom]

I would just create a report and place the totals in the report footer. If
you really need to do this in a query, you could create another crosstab
that doesn't use UnitNo, UnitName, and RegionNo as Row Headings. You could
then create a union query based on these two crosstabs.

Reports are for displaying information so I would lean toward the report
option.

--
Duane Hookom
MS Access MVP


Does anyone know if it is possible to have a column total in a crosstab
query?

Here's my setup:
For RowHeadings I have:
UnitNo
UnitName
RegionNo

For ColumnHeading I have:
Quarter

I'm using Amount as a sum value...

The query looks like this:

UnitNo UnitName RegionNo TotalofAmt Q1 Q2 Q3
166 NewYork 100 500.00 125.00 250.00 125.00
275 Virginia 115 75.00 25.00 25.00 25.00

I not only need a total of all quarters (which I get from the TotalofAmt
column, but I would like to total each quarter. For example, I would like
to know that Q1 totals 150.00

Can this be done in a crosstab query, or should I be looking at an
alternative method.

Thanks for any help someone can provide,
Gene