Access Query and PivotTable View

[Guest]

I have generated a query from combination of a few tables. This query has
data in numeric format, often with decimals. The Datasheet view shows the
numeric data with all decimals and fractions. But the PivotTable view ignores
the fraction portion. It does not display digits to the right of decimal
point. Thus, any totaling calculation is also resulting in decimal error.
Any idea why PivotTable in Access is not displaying fraction part in MS
Access? I don't think that this happens in MS Excel.

 

[Vincent Johns]

I think you need to specify a different format for your TRANSFORM field.
For example, consider the following (fake) data:

[Table1] Table Datasheet View:

Table1_ID Row Column Value
---------- ---- ------ ---------
257517133 B 2 2.1
400672779 A 2 85
405408752 A 1 16.7
1380030933 B 2 13.2
1912432754 B 1 0.003
2098911652 B 1 5
2123848444 A 1 3.1415

The following Query will display sums.

[Table1_Crosstab] SQL:

TRANSFORM Sum(Table1.Value) AS SumOfValue
SELECT Table1.Row,
Sum(Table1.Value) AS [Total Of Value]
FROM Table1
GROUP BY Table1.Row
PIVOT "Col " & [Table1].[Column];


For the [Total Of Value] field, I specified a format of "Fixed" and 1
decimal place. (In Query Design View, right-click on the field's column
and open its Properties.) For the Value column I specified a format of
"General number". What was displayed in the [Total Of Value] column
included only the 1 decimal digit I'd specified...

[Table1_Crosstab] SQL:

Row Total Of Value Col 1 Col 2
--- -------------- ------- ------
A 104.8 19.8415 85
B 20.3 5.003 15.3


.... but copying the displayed values gave me 2 digits after the decimal
point in [Total Of Value] (using Access 2000).

Row Total Of Value Col 1 Col 2
--- -------------- ------- ------
A 104.84 19.8415 85
B 20.30 5.003 15.3

I don't know why it did that, but I think that specifying formats will
take care of your problem.

Alternatively, you can translate your numeric values to strings by using
your Crosstab Query as the basis for another Query, in which you can
specify changes, as in the following Query:

[Q_Reformatted] SQL:

SELECT Table1_Crosstab.Row,
Format$([Table1_Crosstab]![Total Of Value],"000.0")
AS Totals,
Format$([Table1_Crosstab]![Col 1],"#.000")
& " ~ " & Format$([Table1_Crosstab]![Col 2],"#.000")
AS Columns
FROM Table1_Crosstab
ORDER BY Table1_Crosstab.Row;

The output (expressed as strings, not numbers) looks like this:

[Q_Reformatted] Query Datasheet View:

Row Totals Columns
------ ------- ---------------
A 104.8 19.842 ~ 85.000
B 020.3 5.003 ~ 15.300


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.