Re: Access Query and PivotTable View
- From: Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 15 Mar 2006 20:54:07 GMT
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 <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.
punter_maharaj wrote:
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.
- Prev by Date: Re: Access Report Completely Disregards IsNull in Order By
- Next by Date: Re: Concatenating fields from multiple records
- Previous by thread: Re: Query (expressions) where where I can receive 1, 2, or all records
- Next by thread: Re: Extracting records from a table using a date
- Index(es):
Relevant Pages
|
