|
Field Name
Refer to naming conventions for naming of fields.
Even though Access will allow it, be careful not to use the same field name
in more than table. The only time this could be acceptable is
when the field name is used to tie different tables together. Such as CustID, ItemID, etc.
Data Type/Field Size
| Data Type |
Description |
| Strings (Alpha-numeric) |
| Text |
Any field that needs to contain letters and numbers is
defined as a text field. The maximum length of a text
field is 255 characters. It doesn't matter if you set
the length larger than you may think necessary. Access
does not store or pad with trailing spaces. Zip codes
should be set to a 'text' type to accommodate zip codes
that start with '0'. |
| Memo |
Memo type should be used with caution. It can contain
64,000 bytes of data, but access time of memo fields is
slower than text fields. In addition you can not sort
or search for values in memo fields. Of note: Memo fields can not be sorted. |
| Numeric Only |
| Number
|
You have to select a numeric type in the size field. Types (from smallest to largest):
- Byte range= +1 to +255 (1 byte)
- Integer range= -32768 to +32768 (2 bytes)
- Long Integer range= -2 billion to +2 billion (4 bytes)
- Single floating point with fractional numbers to 7 digits (8 bytes)
- Double floating point with fractional numbers to 14 digits (8 bytes)
The
most common is Integer or Single. Do
not use integer types if you require decimal values.
For efficiency it is important to select the
smallest type that will accommodate the data that needs to be retained. |
| Currency |
Currency type should be used if the field represents a
money value. It automatically selects the correct size
(8 bytes) and decimal places. Currency type will hold
15 digits of whole dollars plus accuracy to the hundreths
of a cent. |
| Auto Number |
Auto Number automatically assigns a unique number to each
record. This type is convenient to use when you want to
make sure there is a unique for a record. (4 bytes) |
| Other |
| Date/Time |
To retain a date and/or time value |
| Yes/No |
This type represents Yes/No, On/Off, True/False. It is
important to understand that Yes,On,True=-1 and
No,Off,False=0 (1 bit) |
| OLE |
Use this type to store an image, spreadsheet, word doucment
within the record. Keep in mind space requirements. (Up to 1 GB) |
| Hyperlink |
Use this type to store a Email or URL address. Allows
you to click on the field in a form to either send email
or open your Internet browser. (up to 2048 bytes) |
top of page
Format
- For Date fields, the most common format is 'Short Date'
- For Time fields, the most common format is 'Medium Time'
- A handy format is a single > character. This will cause the
field to automatically be converted to uppercase when displayed. Example
If this a field used to store the 2 character State Code. Whether
the user enters il, Il or iL, the value will be displayed as IL.
top of page
Decimal Places
Typically, leave this set to auto, unless you want to make sure that only
X number of decimals are shown.
top of page
Caption
Because it is recommend to use short and abbreviated field names, it is a
good idea to enter a readable user-friendly description. When
you create forms and reports, Access will use the caption field
automatically for field labels and report titles.
top of page
Default Value
Most of the
time you will leave this blank.
- Date field that should default to today's date, enter date()
- Yes/No fields - Set the most common choice for the field.
- Numeric fields - It is recommended to at least set the value to '0'.
Leaving a 'null' in a numeric field can cause calculation errors
top of page
Validation
If you want to ensure valid data is entered, you need to set both the validation
text and rule. The rule represents the logic for the validation
check. The text represents the error message to display when the entry is not valid.
top of page
Required
Be careful in using this option. It can create headaches when appending data.
Keep in mind you can always control the entry for a field within a form.
top of page
Allow Zero Length
Be careful in using this option. It can create headaches when appending data.
Keep in mind you can always control the entry for a field within a form.
top of page
Index
Be careful in how many fields you select to have indexed. Indexes are used
to improve the speed of sorting or finding records in a table.
The more indexes you have in a table, the
longer it can take to add or delete records. If you know
that a field will be commonly and frequently used for sorting purposes, then set the index.
There are two options: Duplicates allowed or No duplicates.
If you are defining a table to store customer information, you would set
the CustID (or whatever field represents a unique key) to Not Allow duplicates.
If you are defining a table to store customer invoices with indexed fields
for CustID and Invoice number, you would probably set the CustID
to allow duplicates and the Invoice number field to Not allow
duplicates. This will prevent the possibility of creating more
than one record with the same invoice number and allow a customer to have several invoices.
top of page
Keys
Each table should have a unique key assigned. The key can be a single field
or several fields. Keys, by default, are indexes. Fields
that have indexes are not always keys. To set the unique key
- Select the field or fields that represent the unique value(s)
- Click on the 'key' icon.
- To select more than field hold down the 'control' key while clicking on the field.
- After setting the key fields, make sure you have the indexes set correctly as to allow or not allow duplicates.
top of page
|
