Subscriber only lesson.
Sign up to this course to view this lesson.
Design a Table , Number Data Types
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 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 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 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 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 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 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.
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 is commonly used by engineers, scientists, and mathematicians to express big numbers. In Scientific Notation, numbers are written in the form:
m × 10n
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 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 is a choice for Format and shows numbers with commas (or thousands separator defined in the Windows Region Settings) between thousands.
Go to the Design View of a Table from the Navigation Pane
- Right-click on a table name in the Navigation Pane
- Choose Design View from the shortcut menu.
Go to the Design View of a Table from the Relationships Window
- Right-click on a table in the Relationships Window
- 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
- To change the Data Type of a field, go to the Design View of a Table
- From the Data Type drop-down for the desired field, choose the main data type.
- If the Data Type is Number, then choose the real data type from the Field Size property in the lower pane.
- 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
- To delete a relationship, go to the Relationships Window
- Right-click on the relationship line
- Choose Delete from the shortcut menu.
Change Number Format for Windows
- Press Windows-X to go to the Control Panel
- Choose Control Panel from the shortcut menu
- Choose Region.
- On the Formats tab, choose Additional settings ...
- Click on the Numbers tab.
Lesson notes are only available for subscribers.