Creating Tables

3 4

Tables are the heart of an Access database-they contain the data that is displayed in forms and data access pages and printed in reports. Each table in Access should store data related to a specific category-for example, you might store customer information in one table, product information in another, and vendor information in another. The data in different tables should be related via linking fields, thereby avoiding duplication and other problems.

See Chapter 3, "Introduction to Database Design," for information on normalizing database tables.

Tables store data in columns and rows. Each column is a single data category, or field, and each row constitutes a record, which contains a collection of field entries related to that record. In addition to creating tables to store and organize data, you will build relationships among tables by linking them on key fields. In this section, you'll learn to create tables both manually and using the Table Wizard.

Using the Table Wizard

Access includes a Table Wizard that helps you create tables easily, especially tables that store certain commonly used types of data. The Table Wizard offers a selection of Business or Personal tables that you can use as is, or modify to suit your needs. You can start the Table Wizard two different ways from the Database window:

• Click Tables and then double-click the Create Table By Using Wizard option, or
• Click Tables, click New, and then select Table Wizard in the New Table dialog box (see Figure 4-2); then click OK.

  

  Figure 4-2. Start the New Table Wizard by selecting Table Wizard in the New Table dialog box.

To use the Table Wizard to create a table-in this case, a personal Service Records table, which could be used to record car or truck maintenance-follow these steps:

1. On the first page of the Table Wizard, select the Personal option, and select Service Records in the Sample Tables list.
2. Click the >> button to select all the fields in the Sample Fields list for the table, as shown in Figure 4-3. Click Next.

   

   Figure 4-3. Select the personal Service Records table.

3. Name your table, preferably using the tbl tag as a prefix, as shown in Figure 4-4.

   

   Figure 4-4. Name the new table in the Table Wizard.

4. You have the option of letting Access set the table's primary key (the default) or setting the key manually. Leave the default setting as is so that Access will set the primary key.
5. Click Next to display the next page of the wizard, where you can set links between the new table and existing tables in the database, as shown in Figure 4-5.

   

   Figure 4-5. The wizard suggests relationships to other tables in the database.

6. If you want to set up a relationship to another table in the database, click the Relationships button to open the Relationships dialog box, and select the appropriate option, as shown in Figure 4-6.

   

   Figure 4-6. Choose the way in which you want Access to create the relationship between the new table and the existing table.

   If you select either of the relationship options and click OK, the wizard will create an appropriate linking field (if necessary) and create the relationship.

7. Click Next to display the last page of the wizard, shown in Figure 4-7. This page provides three options: You can open the table in Design view to make further modifications, enter data directly into the table in Datasheet view, or enter data into the table using a form created by the wizard.

   

   Figure 4-7. On this page, choose to modify the table design or choose a method for entering data into the table.

   If you select the third option and click Finish, the wizard creates a form (similar to the form you create by choosing AutoForm from the New Object button's drop-down list) and opens it for editing, as shown in Figure 4-8.

   

   Figure 4-8. A form is created by the Table Wizard for entering data into the new table.

Creating Tables Manually

If the database tables you need to create don't quite match the sample tables available in Access, you'll need to create your own. You can create a table manually two different ways, starting in the Database window:

• Click Tables, and then double-click the Create Table In Design View option, or
• Click Tables and click New. When the New Table dialog box appears, as shown in Figure 4-9, select either Datasheet View or Design View and click OK. The new database table is then displayed in the Access work area. Figure 4-10 shows a new table created in Design view.

  

  Figure 4-9. Open the New Table dialog box to create a table in Design view.

  

  Figure 4-10. Creating a new table in Design view involves naming the fields and choosing their data types.

Troubleshooting - My fields are the wrong data type when I create a table in Datasheet view

When you create a new table in Access, you can choose to create it in Datasheet view or Design view. If you choose to create a table in Datasheet view, you won't be able to select the data type for the fields you create (at least not while in Datasheet view). When you create fields in Datasheet view, Access assigns each field a data type depending on the value you type into that field.

If you type text into a field, it will be a Text field; if you type in a number, it will be a Number field; if you type in a number with a dollar sign, it will be a Currency field; and if you type in a date, it will be a Date field. To create a field using any of the other data types (such as AutoNumber, Memo, or OLE Object), you have to switch to Design view and select the field's data type manually.

It's generally preferable simply to create the table in Design view, where you have all the tools you need to create each field with exactly the right data type. To find out more about setting data types, see the section "Setting Field Data Types" below.

Naming Fields

If you're working in Design view, one of your first tasks is to name your fields. Naming conventions for Access fields are pretty straightforward. See the "Naming Restrictions" section earlier in this chapter for full details on restrictions and recommendations for naming fields-in particular, avoiding spaces and punctuation marks in field names.

To name a field, simply type the field name in the Field Name column for that field, and press Enter or Tab. The selection then moves to the Data Type column.

Setting Field Data Types

In the Data Type column of a table in Design view, you choose the type of data that will be stored in the field. Text is the default data type, but you can choose any one of the types shown in Table 4-2 by clicking the down arrow and choosing the type from the displayed list. When you choose the data type, the options shown in the General tab of the Field Properties section change to allow you to choose specific options for the selected data type.

Table 4-2. Access field data types

Data type	Description
Text	Text data, including numbers that don't require calculations. Text can be up to 255 characters in length.
Memo	Longer blocks of text, up to 65,535 characters.
Number	Numeric data that might be used in calculations.
Date/Time	Dates and times.
Currency	Currency (monetary) values. Also used to prevent rounding off during calculations.
AutoNumber	Creates unique sequential (incrementing by 1) or random ID numbers and replication IDs.
Yes/No	Two-valued data, such as Yes/No, True/False. Null values are not allowed.
OLE Object	OLE objects such as Word documents or Excel worksheets. Hyperlinks (either URLs or UNC paths).
Hyperlink	Hyperlinks (either URLs or UNC paths).
Lookup Wizard	Not really a data type. Allows you to select a value for a field from another table or a list of values.

Although choosing the data type you need may be fairly obvious (such as choosing the Text data type for a LastName field), some choices may not be so cut and dried. Should you use Number or Currency for a field that will store monetary values? When is it appropriate to use the AutoNumber data type? The following guidelines will help you determine when to use different data types:

• For storing money values or numeric values in which a high degree of accuracy is required, use the Currency data type.
• For storing text up to 255 characters in length or numbers that aren't going to be used in calculations (such as Social Security numbers or phone numbers), use the Text data type.
• For storing large amounts of text (more than 255 characters, but fewer than 65,536 characters), use the Memo data type.
• For storing whole numbers between -32,768 and 32,767, use the Number data type with Integer selected for the FieldSize property.
• For storing longer whole numbers, use the Number data type with Long Integer selected for the FieldSize property.
• For storing single-precision numbers that might have fractional values, use the Number data type with Single selected for the FieldSize property.
• For storing double-precision numbers that might have fractional values, use the Number data type with Double selected for the FieldSize property.
• If you need a unique, incrementing ID number to use as a key field, use the AutoNumber data type with Increment selected as the NewValues property.
• If you need a unique, random ID number to use as a key field, use the AutoNumber data type with Random selected as the NewValues property.
• If you need to perform calculations on numbers in a field, use a Currency field or a Number field with the appropriate field size.
• If you need a globally unique identifier (GUID) for replicated databases, use the AutoNumber data type with Replication ID as the FieldSize property setting.
• If you need to enter dates in fields, use the Date/Time data type.

Choosing Number Data Types

After you assign the Number data type to a field you have created, you have to specify the FieldSize property, which determines the type and size of the numbers that can be entered in the field. You can choose from among the following FieldSize choices:

Byte Whole numbers from 0 through 255

Integer Whole numbers from -32,768 through 32,767

Long Integer (default) Whole numbers from -2,147,483,648 through 2,147,483,647

Single Numbers from -3.402823E38 through -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values

Double Numbers from -1.79769313486231E308 through -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 through 1.79769313486231E308 for positive values

Replication ID Globally unique identifier (GUID), a 16-byte field that is a unique identifier for replication

Decimal Numbers from -1038-1 through 1038-1 (ADP), or from -1028-1 through 1028-1 (MDB)

In VBA (and other programming languages), these are separate data types. When you see an Access field data type described as Double, for instance, this means that it is a Number field with its FieldSize property set to Double.

tip - Describe field contents

---

The Description column in Design view is an optional property you can use to describe the field's data. To enter a description, simply click in the property and type the Description text. The description you enter will appear both in Design view and also on the status bar when the user selects the field on a form. When you are working with foreign key fields, for example, you could enter in the Description column the name of the linked table.

Creating Lookup Fields

While you're working in Design view, you can create a lookup field, which enables you to display a list of values from a field in another table. To do this, you use the Lookup Wizard, which is available when you specify the data type for the selected field. To create the lookup field using the Lookup Wizard, follow these steps:

1. Choose the Lookup Wizard data type for a field. The first page of the wizard gives you the option of looking up the values in a table or query or entering them yourself (see Figure 4-11). To create the lookup, click the first option; then click Next.

   

   Figure 4-11. The first page of the Lookup Wizard gives you the option of looking up values or entering them manually.

2. Select the table or query that contains the data you want to look up, and click Next.
3. In the Available Fields list, choose the field that contains the values you need to look up (see Figure 4-12). Click the arrow buttons to move the field to the Selected Fields list, and click Next.

   

   Figure 4-12. Select from the Available Fields list the field you want to use to look up values.

4. If you want to adjust the width of the lookup column, drag the right edge of the column to the desired width; then click Next.
5. On the last page of the wizard, give the lookup column a name. Then click Finish to finish creating the lookup field.
6. A message box appears, telling you that the relationships needs to be saved with the table. Click Yes to complete the operation. Design view is displayed once again, with the lookup information displayed in the Lookup tab of the Field Properties area (see Figure 4-13).

   

   Figure 4-13. After completing the Lookup Wizard, you see the finished lookup field's properties sheet.

Now, when you are working in Datasheet view, you can select a value for this field by picking an item from a drop-down list, which saves time and prevents data-entry errors. When a lookup field is dragged to a form, it appears as a combo box with the lookup table as its row source.

Working with Nulls and Zero-Length Strings

Null is a special value that indicates missing or unknown data. In VBA code, the Null keyword is used for a Null value, whereas a zero-length string is a string that has zero characters. Primary key fields can't contain Nulls, and you can set the Required property to Yes to prevent storage of Nulls in other fields. (A field with Required set to Yes must have an entry; if Required is set to No, the field can contain a Null.)

To prevent a zero-length string being stored in a Text field, set the AllowZeroLength property to No. Figure 4-14 shows a Text field with the Required property set to No and the AllowZeroLength property set to Yes so that both Nulls and zero-length strings can be stored in the field. These settings are appropriate for a field that may have no entry in some records in a table, such as EmailAddress in a contacts table. Initially, when a record has been created and no value has yet been stored in the field, it will contain a Null.

Figure 4-14. Allowing Nulls and zero-length strings in a Text field guarantees that you won't have problems when a record has no data in this field.

When you look at a field in Datasheet view (or in a form), you can't tell the difference between a Null and a zero-length string, but you can use the IsNull function to determine whether a field contains a Null value and the Nz function to convert Nulls to zero-length strings to avoid problems when storing data in variables.

note

---

If you aren't familiar with VBA code, you may want to return to this code segment after reading Chapter 20, "Customizing Your Database Using VBA Code."

The If...Then portion of the following event procedure displays a message box telling you that a field shown on a form contains a Null. The procedure then uses the Nz function to store the contents of the field in a String variable to prevent an Invalid use of Nulls message when you're attempting to store a Null in a String variable. The String variable is then displayed in a message box.

 Private Sub cmdTest_Click()     Dim strDescription As String         If IsNull(Me![Description]) Then         MsgBox "Description contains a Null"     End If         strDescription = Nz(Me![Description])     MsgBox "Description: " & strDescription     End Sub 

Figure 4-15 shows the test form, with the Test command button that runs the preceding code.

Figure 4-15. The test form tests for Nulls and zero-length strings in a field.

When you're working in VBA code, it's advisable to use the Nz function before you store data in variables (especially if there's any chance the data might contain a Null) because the only VBA data type that can accept Nulls is the Variant data type.

Setting Validation Rules

If you want to ensure that users can't enter incorrect data in a field, you can set a validation rule for data entry, with a message to be displayed in case the wrong type of data is entered in the field. For example, if you want to ensure that a positive number is entered in a number field, you could create a field validation rule of >0, which specifies that values greater than zero must be entered. Validation rules can be set in tables or in forms, but generally it's better to set them in tables because validation rules set in tables will be inherited by fields placed on forms, and additionally they will work if data is entered directly into a table.

A validation rule that applies to only one field is called a field validation rule, and a validation rule that includes conditions on two or more fields in a record is called a record validation rule. Both types of rules are specified by the ValidationRule property of a field.

You can either type a validation rule directly in the ValidationRule property or click the Build button to the right of the property to open the Expression Builder dialog box, where you can easily create a complicated expression by selecting components from lists of database objects, fields, and functions. Figure 4-16 shows a simple expression being created in the Expression Builder-in this case, a validation rule that checks whether the date entered in the Due field is later than tomorrow.

Figure 4-16. You can construct a validation rule in the Expression Builder.

Figure 4-17 shows the validation rule and its validation text on the General tab in the Due field's properties sheet.

Figure 4-17. Use a validation rule and validation text in a field's properties sheet to enforce and document the field's data requirements.

Creating Input Masks

In addition to validation rules, Access provides another tool you can use to enforce correct data entry: input masks. An input mask shows the user a template for data entry and won't accept data that doesn't fit the template. Access provides a list of commonly used input masks that you can select from the Input Mask Wizard, and you can also create your own custom input masks.

To specify an input mask using the Input Mask Wizard, follow these steps:

1. Open a table in Design view, and select a field.
2. In the Field Properties pane, click in the Input Mask property. A Build button appears to the right of the property.

   tip

   ---

   To get detailed help on constructing input masks, press F1 while your cursor is in the InputMask property, or type InputMask into the Ask A Question box on the main Access menu.

3. Click the Build button. The Input Mask Wizard starts.
4. On the first page of the wizard, select the appropriate input mask, as shown in Figure 4-18, and then click Next.

   

   Figure 4-18. Select the Social Security Number input mask for a field.

5. On the next page of the wizard, shown in Figure 4-19, select a different placeholder character if desired. (The underscore is the default placeholder character.) You can also edit the input mask on this screen, if desired, by clicking in the Input Mask box and typing the mask the way you want it to appear. Click Next.

   

   Figure 4-19. Select a placeholder character for the input mask.

6. Now you can specify whether to store the data with or without the symbols in the mask (such as the dashes in Social Security numbers). Make your choice and click Next.
7. On the last page of the wizard, click Finish to write the input mask to the InputMask property of the field, as shown in Figure 4-20.

   

   Figure 4-20. View the input mask in the InputMask property of the field.

   Figure 4-21 shows a table in Datasheet view, with the input mask visible in the SSN field.

   

   Figure 4-21. An input mask is visible in the SSN field of a table in Datasheet view.

InsideOut

You can't override an input mask-for example, if you get a foreign phone number that doesn't fit the U.S./Canadian phone number format or a foreign ID number that has a different format than a Social Security number. If you need to enter data in a certain format most of the time but occasionally in another format, write code in the BeforeUpdate or AfterUpdate event of a form control to format the data instead of using an input mask. (See Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data," for additional information on entering data.)

Creating Indexes

You can set an index on a field in an Access table to enable Access to find and sort records faster. Indexes can be set on a single field or on multiple fields; multiple-field indexes are useful when the first field might have duplicate values and you need a value from another field to create a unique key for the table. If you have a primary key field, which is used to relate a table to foreign key fields in other tables, it has a unique index that won't allow Null values in the field, and requires an entry in every record.

You can also index a field so that it allows duplicates-this type of index is often used in a field that might be used for searching or sorting, such as a ZIP Code field. Figure 4-22 shows the available indexing options for a table field.

Figure 4-22. Use the available selections for indexing a table field.

note

---

You can't index an OLE Object type field because these fields contain objects-such as Excel worksheets or Word documents-rather than data.