Crosstab query needing column averages
in [Help]
|
Prev: Preventing Duplicates in a Continuous Form Bound to N:N JoinTable
Next: Cannot Output Table to csv format
From: The Frog on 15 Jul 2010 10:59 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
From: John Spencer on 15 Jul 2010 11:27 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 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
From: The Frog on 16 Jul 2010 03:37
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 |