Column Totals in CrossTab Query

[Angel_G]

Hi there.
I need to create a crosstab query that will give me the totals per column
(not by row). So that I can include the results in a linked Excel chart.
I am able to create a crosstab query that will display customer complaints
per month and give me a total for the year per item, but I need to know the
total complaints per month as well.
Is that possible in a crosstab query?
Here is my Crosstab query.

TRANSFORM Count(Charts.[QA Defect Code ID]) AS [CountOfQA Defect Code ID]
SELECT Charts.[Defect Code], Count(Charts.[Mfg Date]) AS [Total Of Mfg Date]
FROM Charts
GROUP BY Charts.[Defect Code]
PIVOT DateDiff("m",[Date Received],Date());

Thanks in advance!

 

[Tom Ellison]

Dear Angel:

It is unusual and not extremely easy to do this, but it is possible.

Create a separate query to produce the totals from your data, using
all the columns.

Add a constant column to the original crosstab query and put 1 in this
column.

Add a constant column to the totals query and put 2 in this column.

The above must produce two sets with the same columns in the same
order.

Create a UNION ALL of these two and order it by the added column with
the 1 and 2 in it, so the totals are at the bottom.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts