MS Access Pivot Tables and Significant Figures

[Hoelper]

When creating a Pivot Table in MS Access, why does the table lose significant digits (e.g., 4.45) and only report rounded whole numbers (e.g., 4)? Can this be fixed?

FYI, the database table is already set to decimal.

[ADezii]

When creating a Pivot Table in MS Access, why does the table lose significant digits (e.g., 4.45) and only report rounded whole numbers (e.g., 4)? Can this be fixed?

FYI, the database table is already set to decimal.

I have no idea why you would be using a Decimal Field, but aside from that, the Default Precision and Scale values for a newly created Decimal Field in a Table are 18 and 0 respectively, which means that the Decimal Component gets truncated or cut off. The good news is, here is the fix:

1. Open your Table in Design View.
2. Select your Decimal Field(s) one at a time.
3. Under Field Properties, General Tab.
4. Set the Scale Value to the number of digits that you would like displayed after the Decimal Point.
5. Good luck.

[Hoelper]

I have no idea why you would be using a Decimal Field, but aside from that, the Default Precision and Scale values for a newly created Decimal Field in a Table are 18 and 0 respectively, which means that the Decimal Component gets truncated or cut off. The good news is, here is the fix:

1. Open your Table in Design View.
2. Select your Decimal Field(s) one at a time.
3. Under Field Properties, General Tab.
4. Set the Scale Value to the number of digits that you would like displayed after the Decimal Point.
5. Good luck.

Thank for your quick response! However, my problem isn't with the database table properties, but rather with a Pivot Table's properties. For some reason, when the database table is set to decimal with precision and scale set appropriately, the Pivot Table does not recognize the decimal values, which are housed in the database. Instead it rounds the values to whole numbers.

[ADezii]

Thank for your quick response! However, my problem isn't with the database table properties, but rather with a Pivot Table's properties. For some reason, when the database table is set to decimal with precision and scale set appropriately, the Pivot Table does not recognize the decimal values, which are housed in the database. Instead it rounds the values to whole numbers.

Sorry for misinterpreting , I'll look into the problem further.

[ADezii]

Thank for your quick response! However, my problem isn't with the database table properties, but rather with a Pivot Table's properties. For some reason, when the database table is set to decimal with precision and scale set appropriately, the Pivot Table does not recognize the decimal values, which are housed in the database. Instead it rounds the values to whole numbers.

I created a Pivot Table utilizing the DECIMAL Data Type and experienced the same problem. When I changed the Data Type to DOUBLE, all was fine and the numbers displayed properly. The only thing that I can see is that the Pivot Table is having problems accepting the DECIMAL Data Type and thus converts it to a whole number. This Data Type is unique in that it is not intrinsic within Access. You cannot Declare a Variable as Type DECIMAL in Access. The following line will generate an Error:
[CODE=vb]Dim decSomeNumber As Decimal[/CODE]
DECIMAL exists only as a Variant Sub-Type. The following code will compile:
[CODE=vb]Dim decSomeNumber As Variant
decSomeNumber = CDec(23344.6534 344988)[/CODE]
You should also be aware that this Type requires 12 Bytes of storage as opposed to 8 for DOUBLE, and 4 for SINGLE. Now that I bored you with details, just for curiosity, why are you using this Data Type in the Pivot Table?

[juanchodavid]

This problem appears whenever a DECIMAL field is used within the pivot table. Solution: convert the field to double. ex: SELECT CDbl(table.fiel d). Where table is the name of your table, field the field of type decimal. After adding that conversion the pivot table should work properly.

[smileykmb]

Another Solution

I couldn't figure out how to program the different data type. So I exported the data to Excel, and created a pivot table in Excel. Excel had no problem reading all the decimals!