After a brief hiatus, our series explaining the ins and outs of using Microsoft Access 2003 continues. This installment takes you from creating tables to hold data to enforcing referential integrity on those tables.
Contributed by Sage Adams Rating: / 13 December 22, 2004
You created database structures in our last lesson. Now it’s time to make tables to hold your data. Before you start creating your tables however, it’s a good idea to plan your table designs carefully. You should decide what kind of information your tables will contain and the relationships between your data. The importance of table design cannot be overestimated: good table design helps make error-free databases possible, while bad table design creates flawed and unusable databases. Access gives you tools to help with table design, and that is a major topic of this week’s lesson. We will also cover table relationships in some detail.
Using the Table Wizard
It’s time to create your first table using the Table Wizard. Open the Tables page in the Database window and double-click the Create Table by Using Wizard icon; a dialog box will pop up. The dialog shows two lists of sample table types: business and personal. Select the one you want and then choose from the list of sample tables below. Then you’ll select either some or all of the fields from that sample table, to the right, for use in your new table. You may rename some of the fields if you like using the Rename Field button. When you have selected and renamed your desired fields click "Next." The Table Wizard dialog box looks similar to the following:
A second dialog box lets you name the table and also set the primary key. You have the option of letting Access assign the primary key for you, in which case the key will automatically comprise consecutive AutoNumber values. Alternatively, you can set the primary key yourself. Click "Next" when you have finished.
If you opted to set your own primary key, the following dialog box asks you to name your primary key field. You will also be asked to set the data type for the field. Consecutive numbers are usually your best option here because Access automatically assigns unique values to this field, and it’s important that your primary key values be unique.
The next dialog box asks you if you want to relate your new table to pre-existing tables, if you have any.
The final dialog box gives you the option of going directly to the Design View of the table, opening the Datasheet View, or having the wizard create a form for data entry. The design view is where you determine your field names, data types, and field properties. The datasheet view is a columnar data entry view similar to Excel. Forms are a more attractive and easier method of putting data into your tables. We’ll cover forms in a later lesson. Select whichever option you like best and click "Finish."
To create a table without the wizard just double-click the Create Table in Design View icon in the Tables page. The Table Design View is segmented into several panes: a field entry pane (top), a field properties pane (bottom-left), and a description pane (bottom-right). You’ll also notice some new buttons on the Table Design toolbar such as Primary Key, Indexes, and Insert Rows. Take some time to explore these new options. The Create Table in Design View screen looks similar to the following:
To add fields to the table design, click into a blank row and enter a name for the field. Try not to include spaces in your field names as this might cause problems later if you need to export your Access data into another program.
Next, select a data type from the drop-down list. The type you choose depends on what you will be using the field for. Text is the default value, but you don’t have to make every field a text value. Some of your other options include: Number, AutoNumber, Date/Time, OLE Object, and Hyperlink. If you’re not sure what type of data you want to have in a field you may just choose to use the Access Field Builder to help you out. Open the Access Field Builder by highlighting an empty row and clicking the "Field Builder" button in the toolbar. You will be taken step-by-step through the process of creating your field.
When you’ve chosen your field name and data type it’s time to put in a short description of the field. This is optional but useful if other people will be using your database, or if you have a tendency to forget why you named a certain field the way you did (with an abbreviation for example).
Next, set the properties for the field in the lower-left properties pane. Field properties determine how your table values are stored and displayed. You can set the following properties of a field: size, format, input mask, caption, default value, validation rule, validation text, required, allow zero length, indexed, Unicode compression, IME mode, IME sentence mode, and smart tags. The following list describes the properties in more detail:
Field size sets the maximum amount of characters you can have in a field.
Format specifies the display of data such as forcing all values to UPPERCASE.
Input Mask creates a template for data entry that requires data to conform. You might force all entered telephone numbers to have the following format for example: 000-000-0000.
Caption displays a name other than the field name for forms, the datasheet view, and reports.
Default value always automatically enters a value into the field.
Validation Rule sets an expression that checks for invalid data in the field.
Validation Text displays a message if the validation rule is violated.
Required just means the field cannot be left blank.
Allow Zero Length distinguishes between a blank field and an empty string field.
Indexed indicates a field upon which the table is indexed.
Unicode compression compresses Unicode field data to save storage space.
IME Mode and IME Sentence mode deal with East Asian text specifications.
Smart Tags recognizes and labels fields as a specific type.
Set a field as the primary key by selecting the field, or fields, and clicking the Primary Key button in the toolbar. You should have a primary key in every one of your tables. This is important because a primary key ensures that each row of data is unique. This ensures that your tables do not contain replication errors, for instance. Generally you’ll want to use the AutoNumber data type for your primary key fields but you don’t have to.
Indexes
An index basically helps you find and sort records quicker. Primary keys are always indexed, but other fields are not. Choose fields you will search among frequently for particular values and index them by changing the index property of the field to Yes. You will also have to decide whether to permit duplicate values or not.
Changing the Design
At some point you’ll probably need to change your table design somewhat, even if you designed it properly in the first place. It’s a good idea to make a backup of your database before you make changes to the design. Even so, Access prompts you when you make changes if the changes are likely to result in data loss.
Some of the design elements you can change include adding or deleting fields, field order, field name and type, or field size. To change the primary key just select the row you want to be the new primary key and click the Primary Key button.
Table Properties
You can set two types of table properties in Access: table object and table definition properties. Object properties include such things as owner and date of creation. Table definition properties are those that relate to the structure of the table itself. To change the default table properties choose Tables | Options and then click the Tables/Queries tab and make the appropriate changes.
Relating your tables is important because it speeds up routine information retrieval and reduces errors in data entry. Moreover, it is useful because related tables can be used to add corresponding subforms and subreports (more on those in a later lesson). Finally, relationships between tables help enforce data integrity.
It’s best to define relationships between your tables when you first create the tables – before you have any data entered. However, you can define relationships at any time.
To create a relationship between two tables you just need to tell Access which fields they have in common. Generally, in the oft-used one-to-many relationship the parent table has a primary key that relates to a foreign key in another table. A foreign key is essentially a field with values that match those in the other table’s primary key but are not necessarily unique.
If you used the Table Wizard to create your tables you might already have relationships created, but either way you can see by choosing the Tools | Relationships menu option. If no relationships are currently defined you’ll be shown the list of tables and queries dialog box from which you can choose the objects you want to relate. Once you have done so your tables or queries are placed in the Relationships window. The primary key of each object is bolded. You’ll see some new toolbar button options at the top of the window too, such as Show Table and Show All Relationships; explore these new buttons. The Relationships page looks similar to the following from the Northwind Sample Database:
You can draw a relationship line by selecting and dragging a field name (usually the primary key) from one table or query to an identical field name in another table or query (usually a foreign key).
You set referential integrity by right-clicking a relationship line and checking the Enforce Referential Integrity checkbox in the dialog box that opens. Once you set referential integrity you also have the option of checking "Cascade Update Related Fields" and "Cascade Delete Related Records." The first option lets you make changes to the primary key in the parent table; Access will then automatically update the child tables to reflect the change. The second option lets you delete parent records; Access will then automatically delete the child records when you delete their parents. The dialog box to set referential integrity looks like the following:
Microsoft Access 
Getting to Know MS Access 2003, Part 2
