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

Quote:

Originally Posted by 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.

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:

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

Hoelper

Quote:

Originally Posted by ADezii

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:

Open your Table in Design View.
Select your Decimal Field(s) one at a time.
Under Field Properties, General Tab.
Set the Scale Value to the number of digits that you would like displayed after the Decimal Point.
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

Quote:

Originally Posted by Hoelper

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

Quote:

Originally Posted by Hoelper

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:

Expand|Select|Wrap|Line Numbers

Dim decSomeNumber As Decimal

DECIMAL exists only as a Variant Sub-Type. The following code will compile:

Expand|Select|Wrap|Line Numbers

 Dim decSomeNumber As Variant

decSomeNumber = CDec(23344.6534344988)

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.field). 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

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!

MS Access Pivot Tables and Significant Figures

re: MS Access Pivot Tables and Significant Figures

re: MS Access Pivot Tables and Significant Figures

re: MS Access Pivot Tables and Significant Figures

re: MS Access Pivot Tables and Significant Figures

re: MS Access Pivot Tables and Significant Figures

re: MS Access Pivot Tables and Significant Figures

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about