Microsoft Access for Beginners
Part II - Building the Tables
Contents
IntroductionCreating a Table
Field Names
Data Types
Field Properties
Adding a Primary Key
Table Properties
Conclusion
Introduction
You can read the entire Microsoft Access for Beginners series, completely reviewed and updated for Access 2010, in the new Kindle edition now available from Amazon.com!
In Part I, I wrote 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 is available 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 most important items.
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
(Click for example of Access 2007 screen.)
There's often more than one way to do something in Access and this is true of creating tables as well. At it's simplest, you could choose the first option in the dialog shown above, 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.
The screenshot 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.
Field Names
Access field names can be up to 64 characters in length but 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 allows spaces in names but I avoid them, again because it can make the names awkward to deal with in the long run. The names shown above use a naming convention 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.
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 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. | The Text type will not reserve extra 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 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 one 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). On forms, it can also be setup to show 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 directly readable within the table but the object can be 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 one gigabyte. |
| 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. |
Read the entire Microsoft Access for Beginners series offline ...
Microsoft Access for Beginners
Expanded, updated for Office Access 2010 and available from Amazon.com.
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
(build button) or the
(drop down) 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:
|
|
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 useful 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 the more
indexes are present, the more work Access has to do to maintain these 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 combining two or more fields, 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 and managed 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. |
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.
Setting a primary key is as simple as selecting the field(s), right-clicking and selecting the option from the pop-up menu.
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.
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 in the main database window, this value will show up 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 can 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. |
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.
