From: Karen Mills on
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