• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 4. Creating Your Database and Ta... > Creating a Table Using the Table Wiz...

Creating a Table Using the Table Wizard

If you look in the Wedding List sample database (WeddingList.mdb) included on the companion CD with this book, you’ll find it very simple, with one main table and a few supporting tables for data such as titles, cities, and postal codes. Most databases are usually quite a bit more complex. For example, the Proseware Housing Reservations sample database, also included on the companion CD, contains six main tables, and the LawTrack Contacts sample database contains more than a dozen tables. If you had to create every table “by hand,” it could be quite a tedious process.

Fortunately, Microsoft Access comes with a Table Wizard to help you build many common tables. Let’s move on to a more complex task—building tables like those you find in Law-Track Contacts (and like those you designed in Chapter 3). For this exercise, create a new blank database and give it the name Contact Tracking, as shown in Figure 4-13. You’ll use this database to start building tables like some of those you saw in Chapter 3.

Figure 4-13. Creating a blank database named Contract Tracking.

To build a table using the Table Wizard, go to the Database window, click the Tables button, and then click the New button. In the New Table dialog box (see Figure 4-9), select Table Wizard from the list and click OK. You can also double-click the Create table by using wizard shortcut shown near the top of the Database window in Figure 4-8. You’ll see the opening window of the Table Wizard, shown in Figure 4-14.

Figure 4-14. The opening window of the Table Wizard.

Toward the middle left of the window are two option buttons—Business (to select business-oriented tables) and Personal (to select personal tables). You can find an entry for a Contacts sample table in the Business category. When you select this table, the wizard displays all the fields from the Contacts sample table in the Sample Fields list. (You will change the table you create now in Chapter 5, “Modifying Your Table Design,” so that it is more like the final tblContacts table in the LawTrack Contacts database.)

To select a field, click its name in the Sample Fields list, and then click the single right arrow (>) button to move it to the Fields in my new table list. (You can also select a field by double-clicking its name.) You define the sequence of fields in your table on the basis of the sequence in which you select them from the Sample Fields list. If you add a field that you decide you don’t want, select it in the Fields in my new table list and click the single left arrow (<) button to remove it. If you want to start over, you can remove all fields by clicking the double left arrow (<<) button. If you pick fields in the wrong sequence, you must remove the field that is out of sequence, click the field above where you want the field inserted, and then select the field again.

Many of the fields in the Contacts sample table are fields you’ll need in the Contacts table for your Contact Tracking database. You can pick ContactID, FirstName, LastName, Address, City, StateOrProvince, PostalCode, Country/Region, WorkPhone, WorkExtension, and EmailName directly from the Contacts sample table.

In this Contact Tracking database, you want to track some home contact information, so you need to find a second set of address fields in another sample table to use in your new Contacts table. You also need a field for middle name or middle initial. In the Sample Tables list, select the Employees sample table. You can see a MiddleName field here that you can use, and some more address fields while you’re about it. Click the LastName field in the Fields in my new table list to indicate that you want to insert a field after LastName, and then double-click the MiddleName field to insert it after LastName. Similarly, click the WorkExtension field in the Fields in my new table list, and then add Address from the Sample Fields list (which the wizard will rename Address1 because you already have an Address field), City, StateOrProvince, PostalCode, and Country/Region—you’ll use these fields to store the contact’s home address information. Also, below EmailName, add Photograph, Notes, SpouseName, and two copies of the Birthdate field from the Employees sample table. (The wizard will name the second one Birthdate1 for now.)


The sequence of FirstName, LastName, and MiddleName might not seem exactly logical, but I did it this way on purpose so I could show you how to move fields in a table design in the next chapter.

Now you need to rename some of the fields. Rename the first Address field to WorkAddress by clicking that field in the Fields in my new table list and then clicking the Rename Field button. The Table Wizard opens a dialog box to allow you to type in a new name. You need to rename the rest of the first set of address fields with a “Work” prefix and then correct the names of the second set of fields to use as home address information by adding a “Home” prefix. While you’re at it, also remove “/Region” from the Country/Region fields. Figure 4-15 shows how to rename the second Birthdate field to SpouseBirthDate. As you can see, it’s easy to mix and match fields from various sample tables and then rename the fields to get exactly what you want.

Figure 4-15. Choosing fields from different sample tables and renaming fields.

Click the Next button to see the window shown in Figure 4-16. In this window, you can specify a name for your new table. (Because you started by choosing fields from the Contacts sample table, the wizard suggests that you name the table “Contacts,” which is just fine.) You can also ask the wizard to set a primary key for you, or you can define your own primary key. In many cases, the wizard chooses the most logical field or fields to be the primary key, but you can ensure that it picks the right one by specifying that you want to select it (No, I’ll set the primary key). If the wizard can’t find an appropriate field to be the primary key, it creates a new primary key field that uses a special data type called AutoNumber. As you’ll learn later in this chapter, the AutoNumber data type ensures that each new row in your table will have a unique value as its primary key.

Figure 4-16. Specifying a table name and selecting a primary key option in the Table Wizard.

Inside Out: Why you should always pick the primary key in the Table Wizard

The wizard might pick the wrong field as the primary key, particularly when you’re using the Table Wizard to define a table that contains both an identifier field for the table as well as fields that you’ll use to link to other tables. For example, if you build an Employees table and include both the EmployeeNumber field (that you intend to use as the primary key) and the DepartmentID field (to link later to a Departments table), the wizard incorrectly picks DepartmentID as the primary key of the table. It seems to prefer fields that end in ID over ones that end in Number.

Go ahead and choose the option to pick your own primary key and then click Next. The Table Wizard displays the window shown in Figure 4-17. You can open the list at the top of the window to select any field that you defined. This is a Contacts table, so ContactID is the appropriate field to choose. If you choose the first option for the type of key, the wizard uses an AutoNumber data type that I’ve already mentioned. The second option causes the wizard to create a number field into which you must enter a number for each record, and the third option gives you a text field for each record. In this case, pick the first option.

Figure 4-17. Identifying the field you want as the primary key.

For details about the different data types that you can assign to fields in your tables, see “Defining Fields,” page 100.

Click the Next button to move to the next window. If you have other tables already defined in your database, the Table Wizard shows you a list of those tables and tells you whether it thinks your new table is related to any of the existing tables. If the wizard finds a primary key in another table with the same name and data type as a field in your new table (or vice versa), it assumes that the tables are related. If you think the wizard has made a mistake, you can tell it not to create a relationship (a link) between your new table and the existing table. You’ll learn how to define your own relationships between tables later in this chapter.

Because this is the first and only table in this database, you won’t see the Relationships window in the Table Wizard. Instead, the wizard shows you a final window in which you can choose to modify the table design, open it as a datasheet, or call another wizard to build a form to edit your data as shown in Figure 4-18.

Figure 4-18. The final window in the Table Wizard offers you three options.

Select the Modify the table design option and click Finish to let the wizard build your table. The table will open in Design view, as shown in Figure 4-19. In the next chapter, you’ll learn how to modify this table in Design view to exactly match the tblContacts table in the Law-Track Contacts database. For now, close the Table window so that you can continue building other tables that you need.

Figure 4-19. The Contacts table built using the Table Wizard.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint