[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1143
  • Last Modified:

Storing a fraction as a decimal

Our data warehouse stores values as decimal because they are packed.... so 12 would be stored as one byte. In access I have tables whose elements contain data in the format of  .65474 or .5454332. My access tables are currently stored as double... However, I am going to be feeding this information into a db2 table that stores these values as a decimal. I want to make my access tables consistant and store my data from double (stored as .65474) to decimal. Will this cause any problems???? I am not sure if decimal as fieldtype will change the format of current values like .65474. I have done a search on this site and did not find any similar questions.... Nor did I find anything for this when researching fieldsizes... I am very concerned about the shift in data format and calculations if I change from double to decimal.... THANks for the help
0
brontes1
Asked:
brontes1
  • 5
  • 3
  • 2
2 Solutions
 
Steve BinkCommented:
Access does not have an inherent decimal type.  Per the help file, decimal data types are actually variant data types, sub-typed to appear as decimal.  You have no troubles converting from one to the other.  See CDec and CDbl in the VB help for more information.

As far as formatting, the data in the table is not where you need to be concerned about formatting.  You can handle formatting during presentation of the data such as in forms or reports.
0
 
Steve BinkCommented:
One other thing: changing the data type of the value will NOT change the value, except for matters of precision and scale where rounding is necessary.  Since decimals can have up to 28 decimal places, and double can hold better than 300, I do not think you will have an issue in this regard.
0
 
brontes1Author Commented:
Does decimal funtion the same as it does in db2... meaning packing values as 12 would be stored as one byte??? I am not sure what variant type is?? I am assuming not as was stated appear as a decimal.... Thanks again
 
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Steve BinkCommented:
Variant data in Access could best be explained as a 'wildcard' data type.  It can hold any type of data to begin with, and will automatically translate to whatever type is immediately necessary.  Since VB also has implicit conversion between similar data types, it seems to me a bit redundant.  Perhaps another expert would be kind enough to fill in a more in-depth detail of its uses.

The end result is that data coming from Access is 'standardized' to be available in any format you desire.  In the event that implicit conversion does not work, VBA provides for explicit conversion through CStr, CDec, CDbl, and so on.  The decimal 'subtype', as it is referred to in the help, is nothing more than a catch-all for other platforms that do have a distinct decimal type.

For a better clue as to how different types relate to each other across platforms, you will need to look at scale and precision of the type on both Access and the other platform.  For example, the link below details comparisons between Access and Oracle:

http://support.microsoft.com/default.aspx?scid=kb;en-us;104977

Precision is defined as the total number of digits to be stored, and scale is the number of digits to the right of the decimal.  
0
 
Gustav BrockCIOCommented:
For storing decimals with, say, eight digits, double is fine.
However, calculations can cause troubles. The classic is:

  dblDiff = 0.011 - 0.010

- should be 0.001, but ...

One option is to scale a field with data type Currency as this carries four decimals with no problems.
To save a fraction of eight digits, multiply with 10000. To retrieve it later, divide by 10000.

Data type Variant is for VBA only; data types for tables must be specific.

Another option is to user SQL Server (or the scaled down and free) MSDE as the backend. It has Decimal as data type for table fields.

/gustav
0
 
brontes1Author Commented:
One last question I am not sure about still...
If I store a data type as double (just an example) in access and then move it into a db2 table ... same field.... while doing this through access.... can I move the access defined double field into a db2 field defined as decimal??? Will this affect my data?? Example double .082222   moved into a db2 table  as decimal (with 6 place holders?)
Thank you for explaining the decimal in access .... I understand... but not clearly?? Although your explaination was excellent. However, I still dont understand if in access by storing as a decimal if it packs the values as db2 does?? So another words.. would 12 in access be stored as 2 bytes or 1 byte?? IF packed then 12 is 1 byte... if unpacked then 2 bytes right??? Thanks again???
0
 
Gustav BrockCIOCommented:
I'm not sure data is "packed" at all in Access. If you browse the on-line help in Access you'll find an explanation on how many bytes each data type uses in a table field. Byte occupies one .. and so one.

For your double, 0.082222 is not rounded by the limitations of the double data type, so I see no problem moving this back and forth between DB2 (with 6 digit decimal) and Access.

/gustav
0
 
Steve BinkCommented:
cactus_data has the right of it for the rounding issue.  As long as your recipient field has at least the same precision and scale as the donor field, you will have no problems.  For example, I'm storing 5.6974 (scale of 4, precision of 5).  If I move this to another platform using a precision of 10 and scale of 5, my raw data may look like this: 5.69740 (note the extra decimal place).  If I move it to use a precision of 5, but a scale of only 2, my new data looks like this:  5.70.

In the first example, no issue...better precision and scale will never negatively impact your data, though it could impact performance.  The second example has an issue with data integrity in the form of rounding error.  As stated previously, both floating-point types being discussed have plenty of precision and scale for your data, and translation to and from each of them should bring you no grief.

Access uses the same standard storage that other platforms normally use, such as MSSQL.  They aren't "packed" in the traditional sense, though they do take less space to store than to display.  For storage lengths:

byte = 1 byte
smallint (integer in Access) = 2 bytes
integer (long integer in Access) = 4 bytes
char = 1 byte
double = 8 bytes

In any case, how the number is stored is up to the individual application.  324 will be 324, whether it is stored in Access, Oracle, db2, etc.  Since coding databases does not entail having to deal with that kind of low-level conversion, the method of storage is of relatively little concern.
0
 
brontes1Author Commented:
thanks for the extra input... appreciate your response
0
 
Steve BinkCommented:
That's why we're here.  :)  Good luck with the rest of your project!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now