By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,748 Members | 1,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,748 IT Pros & Developers. It's quick & easy.

MS Access Pivot Tables and Significant Figures

P: 2
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.
Jun 12 '07 #1
Share this Question
Share on Google+
6 Replies

ADezii
Expert 5K+
P: 8,750
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.
Jun 12 '07 #2

P: 2
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.
Jun 12 '07 #3

ADezii
Expert 5K+
P: 8,750
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.
Jun 12 '07 #4

ADezii
Expert 5K+
P: 8,750
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
  1. Dim decSomeNumber As Decimal
DECIMAL exists only as a Variant Sub-Type. The following code will compile:
Expand|Select|Wrap|Line Numbers
  1. Dim decSomeNumber As Variant
  2. 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?
Jun 12 '07 #5

P: 4
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.
Feb 15 '08 #6

P: 1
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!
Oct 9 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.