Crosstab query totals row

[cbende2]

Hello all,



I'm doing a little research on crosstab queries and I'm having trouble finding a solution for this...

I would like the totals row to be automated so the user does not have to click the "Sigma" button in the ribbon.


Or... I read something about creating another query that would give me this total row using an expression?

Thanks

[ranman256]

Make a UNION query.
Q1 = your crosstab query
Q2 = a sum query of Q1
Q3 =
select * from Q1
union
select * from Q2

[cbende2]

Ok, I how am I creating Q2, just a simple query and add an expression for total or something?

[ranman256]

start new query , bring in your crosstab query
sum the columns.
BE SURE to add the field TOTAL. Because UNION queries MUST have the exact same field count.

[cbende2]

Wait, I don't even need step 3.

If I open Q2, I can save it with the totals row since it's a normal query.

[cbende2]

What is the point of the Union?

[ranman256]

I thought you wanted the crosstab data, AND the sum data in 1 sheet.

[cbende2]

Q2 is giving me that.

If I use the query wizard to create a simple query of my already created crosstab query, I can add the totals row to the new select query and save it with the totals row.

It's only on the crosstab queries that you can't save the totals row.