Microsoft Access for Beginners - Part II
Building the Tables
Contents
Introduction
Creating a Table
Field Names
Data Types
Field Properties
Adding a Primary Key
Table Properties
Conclusion
Introduction
In
Part I, I talk about designing a database in terms of organizing
the information into a set of tables so that the data will be most accessible.
The next step is to actually build the tables. The table design environment
has a number of features that contribute to a stable database and some of these
features can be confusing to someone new to Microsoft Access or even databases in
general. More information on all of the features in this article can be found
through the help system in Access which I encourage you to become familiar with.
For now, I'd like to point out some of the important points from my own experience.
Sample Files
If you have not already done so, please download the demonstration files:
Demo.zip - Access demonstration database
and original Excel spreadsheet (These files require Access and Excel 2000 or later.)
Creating a Table
As with many other things in Access, there is more than one way to create a table.
At it's simplest, you could choose the first option in the window below, Datasheet
View, and start typing in data. Of course, like many basic solutions, this one offers
the least control over the result. For this article, I'm going to focus on
a much better way to create a table and that is through the Design View screen.
(Click for example of Access 2007 screen.)
The screen below shows the design view for tblPersonnel, the main table in the Personnel
database. Each field in the table is represented by a row in the top half
of the window with additional settings for the field in the bottom half. You
can switch between these two sections by pressing F6. This screen enables you to
completely customize any table in order to get the most advantage from storing your
information in Access.
Back to Top
Field Names
Access field names can be up to 64 characters in length but in most cases, it's
better to keep them just long enough to be meaningful and short enough to be manageable
when using them in queries or code. Access does allow spaces in names but
I avoid them, again because it can make them awkward to deal with in the long run.
The names shown above use what's called CamelCase
where multiple words are joined without spaces but with each initial capitalized.
Some people also use underscores between words.
It's also important to avoid using reserved words such as function names in tables.
'Date' would be a very common choice for a field name but Date() is a function in
Access that returns the current date and this can cause conflicts when referring
to the field. Something like 'Name' is not a reserved word but is not very
specific either.
Back to Top
Data Types
After you name a field, you specify the type of data that will be stored there.
This is so Access can store the information in the proper format. If used
correctly, data type assignments also help Access to store the information more
efficiently with less wasted space and in a way that will enable you to use the
data as needed.
The table below lists the available data types and some pointers on their use.
Again, for full information on data types, refer to the Access help system.
In my comments, I often reference the number of bytes used to store a data type.
This should not be confused with the number of digits allowed in numeric fields.
Access can use one byte of space to store a number up to 255 and four bytes can
store numbers up to 2,147,483,647!
|
Type |
Description |
Comments |
|
Text
|
Stores general text and alphanumeric data including names, formatted numbers that
aren't used in calculations (i.e. phone numbers, serial numbers). Each field
can hold up to 255 characters.
|
One nice thing about the Text type is that it will not reserve space for a field
if you specify a field size larger than the actual data, i.e. a field size of 35
characters when your data only takes up 10. Still, for the sake of accurate
data entry, it's better to specify fields only as large as you need them.
It's easier to increase a field's size after data has been entered than to decrease
it.
|
|
Memo
|
This is another text field for longer entries, up to 65,535 characters (64 KB).
|
While memo fields can hold more information, they also have limitations such as
the lack of lookup functions and some of the formatting features available to text
fields. Heavy use of memo fields can also make your tables less structured
and they should only be used when necessary.
|
|
Number
|
Numeric-only values that may require calculations to be performed on them.
|
When you select the Number data type, you'll see a list of options under the Field
Size instead of a simple number. In effect, these are subtypes that determine
how the number will be stored and how much space each field will take in the database.
It is determined by the maximum value that can be stored in the field and the number
of decimal places that can be used. The Long Integer is the default and can store
whole numbers (no fractions) from 2,147,483,648 to 2,147,483,647. Access uses
four bytes to store a long integer. A normal integer (32,768 to 32,767) only
takes two bytes. Choosing the right type can also help to limit the type of
values entered to prevent mistakes. |
|
Date / Time
|
Date and time values from the year 100 through the year 9999.
|
Access includes some great functions to add and subtract days, months and years
within date fields. Date fields can also be formatted and sorted in a variety
of ways that text fields cannot be. Each field uses eight bytes regardless
of formatting.
|
|
Currency
|
Monetary values with up to 15 digits to the left of the decimal point and four digits
on the right.
|
The currency data type is a specialized number that includes formatting features
for money values. It also eliminates rounding during calculations. Each
value uses eight bytes.
|
|
AutoNumber
|
This is a value that is generated by the database which cannot be updated by the
user. It can be set to start at 1 and increment by 1 for each record or it
can be set to generate a random unique value.
|
This field can be stored as a random or incrementing long integer which takes four
bytes of space. The other option is to use a Replication ID which is a 16
byte alphanumeric value. This second option is only necessary when replication
is used to synchronize data in multiple copies of the database and a large number
of records are created between synchronizations. Mostly, it's fine to use
either an incrementing or random long integer.
|
|
Yes / No
|
A field with only two possible values. Yes or No. Often used behind
checkboxes.
|
This is the smallest of all fields as it only needs to store a simple true or false
value (also known as a boolean field). It can also be setup to use a third
option if necessary.
|
|
OLE Object
|
A generic field used to store objects such as pictures or other files within the
database. The data is not readable within the table unless the object is opened
by its required application.
|
The size of this field depends on the size of the object stored within it although
there are special considerations when storing images because the field does not
compress image data so the images will take up large amounts of space. It's
maximum size is the same as the maximum size of the database; two gigabytes in Access
2000 and later.
|
|
Hyperlink
|
This field type stores a three part text value that enables a hyperlink to be stored
and activated from the table or from a form field.
|
The hyperlink field is broken down into three parts; the text displayed in the field,
the address / subaddress and the screen tip that is displayed when the user moves
the mouse over the hyperlink. Each of these parts can hold 2048 bytes for
a total of 6144 bytes.
|
|
Lookup Wizard
|
|
This is listed with the field types but is actually a wizard that configures the
field to automatically retrieve data from a table or query or to supply a list of
values provided at design time. The wizard determines which of the field types
to assign to the field after the steps of the wizard are completed.
|
Back to Top
Field Properties
For each field in the table, there is a list of settings that you can use to customize
how the database displays and processes the data. This is shown in the bottom
half of the design screen and the list changes depending on the data type of the
field you're working with. The sample below is the HireDate field which has
most of the settings filled in.
Clicking on the
or the
at the end of one of these settings will open up other screens that supply predefined
values or wizards that will guide you through the settings. It's important to pay
attention to these settings whenever you are designing a table. These give
you full control over how data is entered into the table. For more information
on any of these settings, click inside the setting box and press F1.
|
Setting
|
Comments
|
|
Format
|
For number (including currency) and date types, this setting offers predefined formats
for the data being entered. Among other possibilities, currency can be formatted
with or without symbols and dates can be formatted to include weekdays. You
can also define custom formats if you want to add dashes or other symbols to your
data. The advantage of using this setting is that Access will automatically
reformat the data as it's entered.
|
|
Input Mask
|
This is a great setting for controlling data entry. Whenever a user moves
to a field with an input mask, Access places a template inside the field to guide
the user in entering the data. For a short date like the format used in tblPersonnel,
the user would see something like this:
Access also uses the input mask to create a password field where all characters
entered are displayed as "*" as they're typed. As with the Format setting, you can
select from pre-defined masks based on the data type or you can create your own.
Form fields will inherit masks from the table fields they are based on.
|
|
Caption
|
I don't use this setting much because I find it gets in the way more than it provides
a benefit. Any text you enter here will be shown in the table header in place
of the field name or as a caption on forms. I prefer to use the Description property
next to the Data Type selection if a field requires some explanation beyond the
name. Data entered under the Description will show up in the status bar when
the user views the field on a form.
|
|
Default Value
|
This can be great if a field is supposed to hold the current date or another value
that can be predicted. Using this setting when possible speeds up data entry
and reduces error. In addition to holding single values, it can also use calculations
and formulas.
|
|
Validation
|
The validation settings are also great for data entry control. In the example
screen above, this setting uses a formula to test that the date entered is less
than 61 days in the future. A date in the past would pass this test.
A date three months from now would not. The validation text setting provides
an error message that the user will see when the table refuses to accept a value.
This test is only done when entering new data or editing existing data. It
will not cause an error for data entered prior to the rule being setup unless that
data is edited. |
|
Required / Allow Zero Length
|
The database will not save a record that does not have entries for fields where
Required is set to True. I use this sparingly during design as it can be difficult
to test a database with a lot of required fields.
Allow Zero Length is a setting on text fields that will determine if the user is
allowed to enter zero length values ("") for text fields. This needs to be
used carefully because in some cases it can be difficult to tell the difference
between a field where there is no value (null) and a field with a string of zero
length.
|
|
Indexed
|
An index is used by Access to speed up sorting and searching the data in a
field. According to the Access specifications, you can have up to 32 indexes
on a single table although this would create other problems as Access has to do
extra work to maintain indexes. Indexes should not be used on fields such
as Yes / No fields where there are a lot of duplicate values.
Indexes can also be used to prevent duplication of values. The index for a
field is automatically set as unique when choosing the field as the table key.
If it's a composite key, then each field in the key may allow duplications on its
own but the index will not allow the fields to contain a duplicate combination of
values. The collection of indexes on a table can be viewed by selecting Indexes
from the View menu when in table design mode.
|
|
Unicode Compression
|
In Access 2000 and later, text characters are stored with a character set that uses
two bytes for a character instead of one to allow for additional alphabets and symbols.
Unicode compression is used to offset the difference in fields over a certain size.
New users can safely ignore this field and leave it on the default setting
|
|
IME Mode / Sentence Mode
|
These are also settings that new users can pass by. They concern the use and
display of Asian character sets. For more information, refer to the help files.
|
|
New for Access 2007 |
|
Text Align |
This setting determines how the information is aligned in the field; left, right,
center and full-justification. |
Back to Top
Adding a Primary Key
One of the essential tasks of designing a table is choosing one
or more fields to uniquely identify each record. This is referred to as the
table's primary key. Without a table key, it's easy to duplicate information
within the table and impossible to organize your data as shown in the article Organizing the Data
. The primary key is important enough that if you don't set one, Access will
prompt you when you try to save the table design. A table key can be a single
field or a combination of fields (a composite key). In some cases, one field
is not enough to uniquely identify a record so additional fields are needed.
The sample database includes examples of both types.
While designing the table structure for the sample database, I chose to use an AutoNumber
field as the primary key for tblPersonnel. I could have used the Employee
number field as that should be unique and does have a unique index on it.
For the sake of simplicity in this example, though, I decided to let the database
assign the new key for each record by using the AutoNumber. This number is
unique for each record in tblPersonnel and is shared by all of the other tables
in the database that reference this table. Still, the employee number would
also have worked as a unique value.
For tblSkills, more than one field was needed as the table stored
a record not only for each employee but also for each skill the employee has.
The table has one subject, employee skills, but there is more than one attribute
that is required to uniquely define a specific instance; the employee and the skill.
Therefore, I use a composite key of both fields. There will probably be be
more than one record for each employee and more than one employee will have the
same skill but you will not see two records with the same combination of employee
and skill.
More than two fields can be used in a composite key. In tblAbsences, you can
see that three fields are needed; EmpKey, AbsDate and PayStatus. This is in
case an employee is out for a full day, for example, but only has four hours of
paid leave left. Depending on company policy, separate absences might be entered
for the same employee on the same day but with one using the remaining leave time
and the other being unpaid. In this case, all three fields are required for
a unique record.
After you decide on the fields involved, setting the primary key is one of the simplest
tasks in Access. Right-click on the field or select a group of fields and
right-click on the selection. Then choose Primary Key from the pop-up menu
as shown in the picture below. A small key icon will show up at the left of
each field in the key.
After you set the primary key, the fields involved will show as indexed in the field
properties. The primary key is the main index for the table. If it's
a single field key, then the index will not allow duplicates for that field.
For a composite key, each field in the index will allow duplicates on its own but
if you open the index list, you'll see that the combination of fields that make
up the 'PrimaryKey' index is set to not allow duplicates.
Back to Top
Table Properties
In addition to individual field properties. An Access table has a panel of
properties that apply to the entire table. You can view these by right-clicking
anywhere in the table design screen and selecting 'Properties'. The example
above shows the panel with all of the settings used in one way or another.
|
Setting
|
Comments
|
|
Description
|
When Details view is selected, this will show up in the main database window next
to the table name. It can be useful, especially in large databases.
|
|
Default View
|
Most beginning users will stick with Datasheet which shows a simple row and column
format. The Pivot views listed here, though, provide a simple and flexible
way to analyze the data in a table with grouping and totals by specific fields.
|
|
Record Validation
|
In addition to validating the entry in specific fields, you an use the expression
builder to validate fields against each other and setup rules for the entire record.
In the example shown above, an employee's starting date must be on or after his
or her hire date. If the data entry doesn't meet this requirement, the record
cannot be saved.
|
|
Filter
|
By placing a filter on a table, you can restrict the number of records that you
want to view at one time. This example only shows the records for current
employees by filtering on the Current field.
|
|
Order By
|
The default order of records as displayed by the table can also be set for one or
more fields.
|
|
Subdatasheet / Child and Master Fields
|
When viewing the data in a table, you can show subdatasheets for linked tables.
In this case, the Notes table is used to display the notes for each employee based
on the relationship between the two tables. When you select the table or query
to use, the Child and Master fields are automatically set by default. This
can be changed if necessary.
The subdatasheet height setting sets the maximum height of the subdatasheet when
expanded. A vertical scroll bar will be shown if the number of records exceeds
this height.
The subdatasheet expanded setting, when set to Yes, automatically expands the subdatasheet
for each record in the table view.
|
|
Orientation
|
The table can be set to display fields and related controls either left-to-right
or right-to-left.
|
Back to Top
Conclusion
In this chapter, I've tried to give at least a minimal explanation for each of the
settings that you'll encounter in Access table design. With the power that
Access offers comes a certain amount of complexity. There are some settings in the
table design panel that you may not use for a long time, if at all, and still produce
very professional databases. Still, the more features you are aware of, the
more options you will have available to you when building your solutions.
Becoming familiar with the various features of table design and the settings shown
here is actually one of the most straightforward aspects of database design. At
the same time, it includes some important concepts such as proper use of data types
and data entry validation. I'd encourage you to take some time to experiment
with these settings. The more you know, the better your databases will be.
Back to Top
Return to Series Index