From: Karen Mills on 28 Jul 2010 17:27 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/aspnet/f7338bb9-7fa4-4fa8-9e5a-244857b0d9d4/six-free-visual-studio-2010-msdn-memberships-giveaway.aspx
|
Pages: 1 Prev: Stlinkcriteria when opeining report Next: Correctly calling a workday function |