Microsoft Office Tutorials and References
In Depth Information
Choosing Field Types
✦ Defaults: Some fields have the same value for most records. The
Discontinued field in the Products table will be No for most records,
especially when you create the record. (How often would you enter an
item that’s already discontinued?) You can set the default value — the
value that the field starts out with — to the most common value; you
have to change it only for the records that have a different value.
✦ Indexes: If you plan to sort your table or search for records based on
the values in a field, tell Access to maintain an index for the field. Like
the index of a book, a database index helps you (or Access) find
information; Access stores information about the field to speed searches.
Access automatically indexes primary key fields and foreign key fields,
but you can designate additional fields to be indexed.
That’s it. You’re done designing your database!
Choosing Field Types
Now that you know the concepts and procedures for designing a relational
database, here are a few suggestions on choosing field types for your
Choosing between Text and Yes/No fields
Fields that can have only two values (such as Yes and No, True and False,
or On and Off) are also called Boolean or logical values. You can store
Boolean information in a one-letter Text field, using Y and N. But if you use a
Yes/No field, Access can display the information on forms as a check box,
option button, or toggle button.
Another advantage of going the Yes/No field route is that you can easily
switch between displaying the field as Yes and No, True and False, or On
and Off by changing the Format property for the field. Using a custom format,
you can choose any two text values to display instead of Yes and No. You
can display the values Discontinued and Available for a Yes/No field,
Choosing between Short Text and Long Text fields
Text fields are limited to 255 characters; if you need more, use a Long Text
field. An Access Long Text field can contain more than 65,000 characters of
textual information, but the extra elbowroom costs you some versatility. You
can’t index Long Text fields, for one thing, and they can’t serve as primary or
foreign keys. If you plan to sort or search your records by using the contents
of this field — or to use the information in it to relate one table to another —
a Short Text field usually is your best bet. So is brevity. On the other hand,