Please Help: Pivot Table is Rounding off decimals to 0

[markphsd]

I have a basic table that has amounts in currency format..
like 2.33 when I use a the table in a pivotTable and pivotChart it rounds the amount to 2 dollars.

Does anyone have any advice. I like pivot's a lot, but this renders them useless for me.

Mark P.

Bleh

 

[lupins46]

What version of Access are you using?

 

[markphsd]

2003. Does it matter?

Mark P.

Bleh

 

[lupins46]

Who knows? but if I use a different version to test with then it might not be relevant.

Running a crosstab on the Northwind Orders table and summing Freight I don't see the problem you are having.
Are you sure you have the summary statistic set to Sum?

 

[dhulbert]

Mark.

Go to your pivot table, right click on the data area and from the pop up menu select "Field Settings"
This brings you up the options for your data. If you click the button labled "Number.." you can get to the format for the dataand check its set to be either a number with 2 decimal places or currency.

DH.

 

[markphsd]

Dhulbert,

I have done that. ALl i get is 2.00 when it's supposed to be something like 2.48

I've reduced it to just using a table with values I put in, the pivot table isn't based on a query.

lupins46

The pivot table won't even show the real value of the field. To clairfy,i'm not talking about crosstab queries, nor the sum. The table holds the data correctly. To elaborate reports, SQL and all of it show the decimal values, on totals and individual fields.

Mark P.

Bleh

 

[lupins46]

Hi Mark

Sorry I was actually talking about pivot tables not crosstabs - I forgot for the moment.
Access is not very helpful in analysing pivot table issues, i find.
Try exporting it to Excel and you will have more info available on the right-click menus than you get in Access; in particular the field settings, and the ability to go back into the wizard.

 

[markphsd]

I'm not interested in exporting it to excel. This project requires it in Access.

I have been able to get it run properly before. But... i don't know what I changed. I have looked, albiet not very hard, for months.

Mark P.

Bleh

 

[lupins46]

I was suggesting exporting to Excel to diagnose the problem; not as the solution.

 

[markphsd]

Oh, well, that maybe could work, but I'm not an Excel wiz. There's something about when the table gets converted to pivot view it truncates the decimal. I really think it's a setting. If i were to export it to excel i'm sure that the data would just end up truncated like in Access.

Thanks for your input.

Mark P.

Bleh

 

[brettbum]

I'm currently experiencing the same problem that Markphsd describes.

I have a query that pulls numbers (netsales) in currency format. However the numbers to the right of the decimal get dropped off when I flip to pivot table view. The decimals are there in data sheet view, if I export to excel from data sheet view the numbers to the right of the decimal are there.

If I flip it to pivot tale view and export the pivot table, the detail behind the pivot table does not have the numbers to the right of the decimal either.

Very annoying.

My test scenario has a small data set that I can send to excel. The live db however is over 500k lines and can not be worked in excel.

This thread is a bit dated, but I'd like to know if anyone has found the fix for this yet?
I'm running Access 2002 (10.6501.6714) SP3.

Thanks,
Brett

 

[brettbum]

I think I found the answer, looking on a different discussion board.

Please reference Microsoft Knowledge Base Article 310264

http://support.microsoft.com/default.aspx?scid=kb;en-us;310264&Product=acc2002

If you want to save the web click trip, keep reading.

Hope this helps, it worked for me.
This article was previously published under Q310264
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).


SYMPTOMS
When you view an object in PivotTable or in PivotChart view, the data in a decimal field is truncated after the decimal point.
RESOLUTION
Use a data type such as Currency, Double, or Single that supports decimal places.

-or-

Create a query that uses a data-type conversion function, such as CCur, CDbl, or CSng, to convert the decimal field to a different data type. Then, view the query in PivotTable or in PivotChart view instead of the table.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION
The Currency data type most closely matches the behavior of the Decimal data type, as far as internal storage. Both the Currency and the Decimal data types are scaled integer data types. However, the Currency data type is limited to 4 places to the right of the decimal point whereas the Decimal data type supports up to 28 places to the right of the decimal point. If you must store 4 or fewer digits to the right of the decimal point, use the Currency data type.

The Double and Single data types support decimal storage, but both are floating-point data types. Because decimal fractions do not always have exact binary equivalents, using a floating-point data type can result in rounding errors. Use these data types only if you must store more than 4 digits to the right of the decimal point.
Steps to Reproduce the Behavior
Open the sample database Northwind.mdb.
Create a new table in Design view.
Add the following fields to the table: Table: Table1
----------------------------
Field Name: ID
Data Type: AutoNumber
Indexed: Yes (No Duplicates)

Field Name: Amount
Data Type: Number
Field Size: Decimal
Precision: 10
Scale: 4

Table Properties: Table1
------------------------
PrimaryKey: ID
Save the table as Table1.
On the View menu, click Datasheet View.
Add the following records to the table:ID Amount
1 34223.2345
2 234325.9234
3 92452.494
4 3467.3469

On the View menu, click PivotTable View.
Drag the Amount field from the PivotTable field list to the Drop Totals or Detail Fields Here area.

Note that all digits after the decimal point have been truncated, and that the data is displayed as an integer value.

 

[markphsd]

Hmm, I"m not sure if the resolution really works.

Actually using a different machine sometimes works.

Mark P.

Bleh