Drewslair.com - Microsoft Access for Beginners: Building the Tables

Microsoft Access for Beginners - Part II
Building the Tables

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 (Requires Access and Excel 2000 or above)

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 is the least powerful. 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.

Access offers a number of flexible ways to create tables and other objects.

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.

Table design view offers a straightforward interface for customizing every aspect of the table and its fields.

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 but 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.

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

With the data type option, you can select the type of data to be stored.

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 can also help Access to store the information more efficiently with less wasted space.

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.

Type	Description	Comments
Text	Stores general text and alphanumeric data including names, formatted numbers that aren't used in calculations such as phone numbers or combinations of letters and numbers such as 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, usually up to 65,535 characters (64 KB).	While memo fields can hold more information, they also have certain 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 therefore 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.
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 of four bytes. 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 picture 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. 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.

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 slightly 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.

Each field has its own set of properties depending on its type. These properties can be set to ensure the accuracy of the data entered.

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 an be formatted to include weekdays. Beyond the standard formats, you can also define your own 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 correct format. 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 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.
Required / Allow Zero Length	The database will not save a record that does not have entries for required fields. 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. 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.

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 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 design of the table. 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.

In tblAbsences, a composite key of three fields is needed to uniquely identify a specific employee absence.

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, a composite key of both fields is used. 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.

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 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 field properties, the table itself has settings that will apply to all fields.

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 also 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 database solution.

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.

Return to Series Index

Contents