Hi Everyone,

I have a fairly simple crosstab query and the results are good. I have
a need to add a roll-up row to the query where I need to have
'National' averages. I am not sure how to go about this in the same
crosstab query - not in a report.

In short the crosstab is based on a heirarchy of queries that sort,
filter, and add up the various components into a suitable format for
the crosstab to work. The heirarchy is four to five layers deep
depending on which path you choose back to the source data. At the top
of the heirarchy is a single table that is the base of the crosstab,
with all relevant data in the correct form. Is there any way that I
can add a roll-up row without having to use a report?

The crosstab SQL is:

TRANSFORM Format$(Avg([Achieved]),"Percent") AS Done
SELECT qryREPORT_1_MASTER_CUMULATIVE.SEGMENTE
FROM qryREPORT_1_MASTER_CUMULATIVE
GROUP BY qryREPORT_1_MASTER_CUMULATIVE.SEGMENTE
PIVOT qryREPORT_1_MASTER_CUMULATIVE.VS;

Any ideas?

Cheers

The Frog

You can't do both in one query. You will need three queries to handle this.

Query 1 (Your current query)
TRANSFORM Format$(Avg([Achieved]),"Percent") AS Done
SELECT qryREPORT_1_MASTER_CUMULATIVE.SEGMENTE
FROM qryREPORT_1_MASTER_CUMULATIVE
GROUP BY qryREPORT_1_MASTER_CUMULATIVE.SEGMENTE
PIVOT qryREPORT_1_MASTER_CUMULATIVE.VS;

Query 2 (the Rollup query - sligh modification of your current query)
TRANSFORM Format(Avg([Achieved]),"Percent") AS Done
SELECT "NATIONAL" as SegMente
FROM qryREPORT_1_MASTER_CUMULATIVE
GROUP BY "NATIONAL"
PIVOT qryREPORT_1_MASTER_CUMULATIVE.VS;

Query 3 A Union query
SELECT * FROM [Query1]
UNION ALL
SELECT * FROM [Query2]
ORDER BY Segmente="NATIONAL" ASC, Segmente

The UNION query may very well fail with a crosstab. If you can specify the
values returned by VS field in the Pivot clause you can probably speed the
Union query up and ensure that the union query will work.

The other option that I can think of would be to use a make table query based
on query 1 and an append query based on query 2.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The Frog wrote:

Hi John,

Thanks for that. I ended up figuring out the same thing last night and
it works like a charm. I ended up creating a new query for 'National'
that feeds the second crosstab query. I just did all the calculations
in the 'National' query the same as for the one that feeds the first
crosstab, but left out the filters to roll up everything. It seems
weird to have to crosstab a query that produces only a single row of
data, but it is necessary to get it into the right layout for the
union query that ties them together at the end.

I appreciate your time with this, and its good to know that I did it
correctly!

Thanks John

The Frog

I am having problems with results of a crosstab query. The source query results show my values as:

1.75%
1.85%
1.94%
1.95%

etc.,

but the results of the crosstab query are:

1.75
1.85
..0094
1.95

So for one of the columns, it's changing the decimals, but it looks normal in the source query.

Do you know what the problem could be?
Below is the sql:

TRANSFORM Sum([qry_fuel calc].[Fuel%]) AS [SumOfFuel%]
SELECT [qry_fuel calc].[compressor id], [qry_fuel calc].[Compressor Name]
FROM [qry_fuel calc]
GROUP BY [qry_fuel calc].[compressor id], [qry_fuel calc].[Compressor Name]
PIVOT [qry_fuel calc].MEASUREMENT_MONTH;

Thank you,
KM



John Spencer wrote:

You cannot do both in one query. You will need three queries to handle this.
15-Jul-10

You cannot do both in one query. You will need three queries to handle this.

Query 1 (Your current query)
TRANSFORM Format$(Avg([Achieved]),"Percent") AS Done
SELECT qryREPORT_1_MASTER_CUMULATIVE.SEGMENTE
FROM qryREPORT_1_MASTER_CUMULATIVE
GROUP BY qryREPORT_1_MASTER_CUMULATIVE.SEGMENTE
PIVOT qryREPORT_1_MASTER_CUMULATIVE.VS;

Query 2 (the Rollup query - sligh modification of your current query)
TRANSFORM Format(Avg([Achieved]),"Percent") AS Done
SELECT "NATIONAL" as SegMente
FROM qryREPORT_1_MASTER_CUMULATIVE
GROUP BY "NATIONAL"
PIVOT qryREPORT_1_MASTER_CUMULATIVE.VS;

Query 3 A Union query
SELECT * FROM [Query1]
UNION ALL
SELECT * FROM [Query2]
ORDER BY Segmente="NATIONAL" ASC, Segmente

The UNION query may very well fail with a crosstab. If you can specify the
values returned by VS field in the Pivot clause you can probably speed the
Union query up and ensure that the union query will work.

The other option that I can think of would be to use a make table query based
on query 1 and an append query based on query 2.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The Frog wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Six Free Visual Studio 2010 MSDN Memberships Giveaway
http://www.eggheadcafe.com/tutorials...-giveaway.aspx