Locked lesson.

Upgrade

About this lesson

Recognize the different Number Data Types. The actual Data Type of a Number is specified by the Field Size property.

Exercise files

Download this lesson’s related exercise files.

Services_12_start.accdb
1.1 MB Services_12_NumberDataType.accdb
1.2 MB

Quick reference

Design a Table , Number Data Types

Application Terminology

Byte

Default Value

When a number is defined, Access automatically assigns 0 for the Default Value.  This default value should be deleted when creating key fields, unless 0 is really what you want! Keep in mind that if referential integrity is enforced, as it should be, a foreign key with a value of zero (0) will not match up to anything in the main table unless there is a Primary Key in that table with a value of zero.

Double Precision

Double Precision numbers are a good choice when you have a wide range of data and exact precision is not required.  Double Precision numbers can have decimal places and are stored in floating point format.  They should not be used for exact comparisons.  Do not use floating point numbers for key fields or to relate data.

Exponential Notation

Exponential Notation is also called Scientific Notation and is how floating point numbers are stored.

12345e2 = 12345 x 102 = 1,234,500

e3 = thousand

e6 = million

Integer

Integer is a good choice when you have whole numbers that are not big. For instance, you might use Integer to store a Day, Month, or Year.  Integers range from negative 32 thousand to positive 32 thousand. 

Long Integer

Long Integer is the real data type of most AutoNumbers.  Long Integer is a good data type for Primary Keys because they can range from negative 2 billion to positive 2 billion.  The related Foreign Key will also be a Long Integer.

New Values

New values for AutoNumbers can be set to Increment or Random and are automatically assigned.  When an AutoNumber is a Long Integer, Increment is the default choice.  This means numbers will be sequential.  If a new record is started but not saved, that number will be skipped unless the database is compacted before another record is created.  If records are deleted, there will be gaps in the sequence.  This is ok.  The only requirement is that each value be unique.

Null

Null is the absence of a value.  This is useful to determine if known data has been entered.  There is no such thing as a "Null value" as Null means there is no value.

Number Data Type

The actual Data Type of a Number is specified by the Field Size property.

Primary Key

The Primary Key field of a table must have a unique value on every record.  Primary Key fields usually use AutoNumber, which is a special form of Long Integer.

Scientific Notation

Scientific Notation is commonly used by engineers, scientists, and mathematicians to express big numbers. In Scientific Notation, numbers are written in the form:

m × 10n

Where:

m is called a coefficient and can be any real number.  This is also called the mantissa

n is the exponent and means 10 to the power of n

Single Precision

Single Precision numbers are stored in floating point format and should not be used for exact comparisons. Like double precision, they are not exact.  They can hold decimal places but most of the time, if you want to use a floating point number, Double Precision is a better choice.  Do not use floating point numbers for key fields or to relate data.

Standard Format

Standard Format is a choice for Format and shows numbers with commas (or thousands separator defined in the Windows Region Settings) between thousands.

Steps

Go to the Design View of a Table from the Navigation Pane

  1. Right-click on a table name in the Navigation Pane
  2. Choose Design View from the shortcut menu.

Go to the Design View of a Table from the Relationships Window

  1. Right-click on a table in the Relationships Window
  2. Choose Table Design from the shortcut menu

Go to the Design View of a Table from the Datasheet View

  • When you are looking at the datasheet view of a table, click Design from the Views group on the HOME ribbon.

Change Data Type of a Field

  1. To change the Data Type of a field, go to the Design View of a Table
  2. From the Data Type drop-down for the desired field, choose the main data type.

     
  3. If the Data Type is Number, then choose the real data type from the Field Size property in the lower pane.

     
  4. If the field is part of a defined relationship, the relationship will have to be deleted before the data type can be changed.

Delete a Relationship

  1. To delete a relationship, go to the Relationships Window
  2. Right-click on the relationship line
  3. Choose Delete from the shortcut menu.

Change Number Format for Windows

  1. Press Windows-X  to go to the Control Panel
  2. Choose Control Panel from the shortcut menu
  3. Choose Region.
  4. On the Formats tab, choose Additional settings ...
  5. Click on the Numbers tab.
Login to download
  • 00:04 This is Access 2013, Lesson 12.
  • 00:08 Hi this is Crystal.
  • 00:10 Now that you are getting comfortable with objects
  • 00:12 in Access, it is time to go under the hood
  • 00:15 and see how things are designed.
  • 00:17 A basic property of data is its type ...
  • 00:20 Is it a word? multiple words? pages of words?
  • 00:24 Do they need to be sorted?
  • 00:26 Is it a number? What kind of number?
  • 00:29 Does it have a decimal point?
  • 00:31 How many places does it need?
  • 00:33 Is it a date? a Time? a Date and a Time?
  • 00:37 a Yes/No field? something else?
  • 00:41 In this lesson, we will focus on different number data types.
  • 00:45 We will create a new table with fields
  • 00:47 using different data types.
  • 00:49 We will add records and see how the values that we put in
  • 00:53 are affected by other properties.
  • 00:56 From the Create ribbon, choose Table Design
  • 00:59 in the Tables group.
  • 01:01 The first field will be called ID.
  • 01:04 Because this is not going to be a real table,
  • 01:07 we will not qualify the ID name (i.e. CustID, InvoiceID).
  • 01:10 From the Data Type dropdown, choose AutoNumber.
  • 01:14 The field Description will be ID.
  • 01:17 Right-click on the selector box to the left
  • 01:20 and choose Primary Key.
  • 01:22 That means this field will have a unique value
  • 01:25 for every record in the table,
  • 01:27 and will not allow records to be appended
  • 01:30 that have values that are already in the table.
  • 01:33 In the lower pane, notice additional field properties.
  • 01:37 The Field Size of an AutoNumber is Long Integer,
  • 01:41 which is the real data type.
  • 01:43 AutoNumbers are a special form of Long Integer
  • 01:47 where New Values are automatically assigned.
  • 01:50 Choices for New Value are Increment and Random.
  • 01:54 Increment means the values will start at 1
  • 01:57 and increment sequentially.
  • 02:00 Long Integers can store from negative two thousand million
  • 02:04 to positive two thousand million.
  • 02:07 That makes Long Integer
  • 02:09 a good data type choice for AutoNumber
  • 02:12 because it is unlikely there will be that many records in a table.
  • 02:16 Long Integers take 4 bytes to store, making them small,
  • 02:20 efficient, and an excellent choice for a key field data type.
  • 02:25 The real data type for Number is defined by the Field Size.
  • 02:30 The next field will be NumLng.
  • 02:34 Choose Number and Long Integer for the Data Type.
  • 02:38 If you really want a really big number,
  • 02:40 use the Double Precision data type.
  • 02:43 Double-precision floating point numbers are 64-bits.
  • 02:47 The main thing to understand about floating-point values
  • 02:52 is that they are approximate and not exact.
  • 02:56 You should not compare them using an equal sign.
  • 02:59 For this reason, single and double precision numbers
  • 03:02 would not be good data types for key fields.
  • 03:06 The default data type when importing used to be double.
  • 03:10 This caused problems with Primary and Foreign Key fields.
  • 03:14 Many people never figured out that the data type was
  • 03:17 the reason why data didn't always match when it should have.
  • 03:21 The next field will be single precision number.
  • 03:25 If you have whole numbers and don't need big numbers,
  • 03:28 like you are storing a year, a month, or a day number.
  • 03:32 Integer is a good choice.
  • 03:34 Integers can go from negative 32K to positive 32K
  • 03:39 and can be null. Null means there is no value.
  • 03:43 If the Default Value is 0, however, you would never know
  • 03:47 that a value had not yet been specified.
  • 03:50 For a couple versions, there was no default value for numbers.
  • 03:54 Now it is back, and you have to remember to delete it.
  • 03:58 Delete the 0 out of the Default Value property.
  • 04:02 Save the table. Call it MyDataTypes.
  • 04:05 Switch to Datasheet View and enter information.
  • 04:10 The Field Names are shown across the top.
  • 04:12 ID is an AutoNumber. It will get its value automatically.
  • 04:17 I enter 123.45 into each of the number fields.
  • 04:24 Long Integer and Integer cannot hold decimal places,
  • 04:28 so the fractional part of the number is dropped.
  • 04:31 Single and Double precision numbers can hold decimal places.
  • 04:36 If you are entering big numbers,
  • 04:38 you can use exponential notation.
  • 04:41 12345e2 means 12345 X 10 to the second power.
  • 04:49 This number, however, is too big for Integer.
  • 04:52 The maximum Integer is 32K. We can just enter 12345.
  • 05:00 The maximum positive Long Integer is 2,147,483,647
  • 05:09 When this value is entered into a Single Precision data type,
  • 05:13 it is shown in scientific notation.
  • 05:16 It has more significant figures than single precision (can hold).
  • 05:21 Double precision has 15 significant figures.
  • 05:26 Single precision has 7 significant figures.
  • 05:26 To show commas in big numbers, choose Standard format.
  • 05:29 Since this is a whole number, click Decrease Decimals twice.
  • 05:35 Access now assigns 0 as the Default Value for numbers.
  • 05:38 If this is not desired,
  • 05:41 delete the default value when you are designing the table.
  • 05:44 It is especially important
  • 05:46 to delete the default value of 0 for numeric foreign keys.
  • 05:51 In the next lesson, we will explore text data types --
  • 05:55 Short Text and Long Text.

Lesson notes are only available for subscribers.

Gift this course