Results 1 to 6 of 6

20051201, 04:32 #1
 Join Date
 Apr 2001
 Posts
 304
 Thanks
 0
 Thanked 0 Times in 0 Posts
Explaination of Number Field Sizes (Access 2003)
Can anyone in the know explain the different Field Size options for the Number data type in Access (single, double, decimal, etc.) I assume this all goes back to that high school math class that I didn't excel in...but wondered if anyone could break it down in layman's terms.
Thanks  Sat.

20051201, 04:44 #2
 Join Date
 Dec 2001
 Location
 Melbourne, Australia
 Posts
 4,606
 Thanks
 0
 Thanked 32 Times in 32 Posts
Re: Explaination of Number Field Sizes (Access 2003)
Check <post#=538214>post 538214</post#>

20051201, 04:48 #3
 Join Date
 Apr 2001
 Posts
 304
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Explaination of Number Field Sizes (Access 2003)
Thanks Pat for your prompt reply. Unfortunately, that didn't really clear it up for me.

20051201, 07:40 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Explaination of Number Field Sizes (Access 2003)
If you open a table in design view, click in a number field, then click in the Field Size property in the lower half of the window and press F1, you'll get some help. Here's a summary:
There are 3 whole number data types (without decimal places):
Byte (values 0 to 255; each number takes up 1 byte of storage)
Integer (values 32,768 to 32,767; each number takes up 2 bytes of storage)
Long Integer (values 2,147,483,648 to 2,147,483,647; each number takes up 4 bytes of storage)
Select one of these when you want to store whole numbers; think about the largest number you need to store when choosing between the three. If your field needs to be linked to an AutoNumber field in another table, you must choose Long Integer, for AutoNumber fields are essentially Long Integers.
There are 3 floating point number data types (providing for a fractional part):
Single (stores numbers with 7 significant digits; each number takes up 4 bytes of storage)
Double (stores numbers with 15 significant digits; each number takes up 8 bytes of storage)
Decimal (stores numbers with 28 significant digits; each number takes up 12 bytes of storage)
The latter is not a "native" Access format, it is supported for compatibility with other database systems such as SQL Server. I recommend not using it in your own Access tables.
That Single has 7 significant digits means that it can distinguish between 1234567 and 1234568, or between 1234.567 and 1234.568, but not (accurately) between 12345678 and 12345679, or between 1234.5678 and 1234.5679.
The final data type is Replication ID; this is only used for special purposes.

20051202, 02:28 #5
 Join Date
 Apr 2001
 Posts
 304
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Explaination of Number Field Sizes (Access 2003)
Thanks Hans  that is an excellent summary. I think I'm still a little too daft to get the whole "significant" numbers part of the Single and Double option. BUT....I can't say that I'd ever really use numbers that large (or with that many decimal points) in anything that I'd actually do anyway, so I guess it's okay. Out of curiosity, could you think of an example of when or who would use those options?

20051202, 08:07 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Explaination of Number Field Sizes (Access 2003)
Data types such as Integer only deal with whole numbers such as 2 and 37 and 185.
Data types Single and Double deal with numbers that (can) include fractions, such as 3.141592 or 0.000218 or 46,000,000,000. In order to be able to handle very large and very small numbers, they are stored in a special way that corresponds to the exponential notation you may have seen on calculators. For example, 0.000218 is stored as 2.18E4 and 46,000,000,000 as 4.6E10 (more or less, I won't go into technical details).
Although 0.000218 consists of 7 digits, only the last 3 are "significant", they contain the essence of the number. All those zeros are used to indicate the magnitude of the number. We say that 0.000218 has 3 significant digits.
The Single data type has up to 7 significant digits, you could use it to store 12.34567 or 0.001234567 or 1,234,567,000,000,000. If you store the number 12.345678 as a Single, it'll be rounded to 12.34568, since Single doesn't store more than 7 significant digits. For many purposes that is sufficient, but in repeated calculations, the rounding errors that necessarily occur can accumulate and result in a errors in the 7th or even 6th digit.
The Double data type has up to 15 significant digits, for example 12.3456789012345 or 0.00123456789012345 or 1,234,567,890,123,450,000,000. The rounding errors will be much smaller than with a Single.
Scientists often have to deal with very large or very small numbers, and since precision is very important in science, calculations should be performed with only small rounding errors.