Working with the Northwind Traders Sample Database

One fundamental problem with books about database management applications is the usual method of demonstrating how to create a "typical" database. You are asked to type fictitious names, addresses, and telephone numbers into a Customers table. Next, you must create additional tables that relate these fictitious customers to their purchases of various widgets in assorted sizes and quantities. This process is unrewarding for readers and authors, and few readers ever complete the exercises.

Therefore, this book takes a different track. Access includes a comprehensive but outdated sample order-entry database, Northwind Traders. Rather than create a new database at this point, you create a new table as an addition to the Northwind Traders database. Adding a new table minimizes the amount of typing required and requires just a few entries to make the table functional. The HRActions table you add later demonstrates many elements of relational database design. Before you proceed to create the HRActions table, try the quick example of adding a new contact-tracking table to the Northwind Traders sample database in the following section.

Using the Table Wizard to Add a New Table

Access includes various wizards that simplify the creation of new database objects. Access 2003 includes a Table Wizard that you can use to create new tables based on prefabricated designs for 25 business-oriented and 20 personal-type tables. Many of the business-oriented table designs are based on tables contained in Northwind.mdb or the tables created by the Database Wizard based on one of the database templates.

To review using the Database Wizard to create a contacts table, see "Creating an Access Application from a Template File," p. 55.

The Table Wizard serves as an excellent introduction to the use of Access wizards in general, if you didn't complete the example application in Chapter 2, "Building a Simple Desktop and Web Application." Follow these steps to add a new Jet CustContacts table to Northwind.mdb and add a CustomerID field on which to create a many-to-one relationship with Northwind.mdb's Customers table:

1. If the Employees table is open, close it to make the Database window active. Alternatively, click the Database Window button of the toolbar.

2. Double-click the Create Table by Using Wizard item in the Database window's Tables list to open the first Table Wizard dialog.

3. Accept the default Business option to display a list of tables for business use in the Sample Tables list, and click the Contacts entry to display the predetermined set of field names for the new table in the Sample Fields list.

4. Select the ContactID field, and click the > button to add the field from the Sample Fields list to the Fields in My New Table list.

   Note

   The >> button adds all fields from the Sample Fields list, the < button removes a single selected field from the Fields in My New Table list, and the << button removes all the fields in the Field in My New Table list.

5. Repeat step 1 4 for the FirstName, LastName, Dear, Title, WorkPhone, WorkExtension, HomePhone, MobilePhone, FaxNumber, EmailName, LastMeetingDate, ContactTypeID, ReferredBy, and Notes fields. The Table Wizard's dialog now appears as shown in Figure 5.12.

   Figure 5.12. The first Table Wizard dialog lets you select the table category, the sample table type, and the fields of the select table type to add to the new table.

   

6. Scroll to the top of the Fields in the My New Table list and select ContactID. Then select Customers in the Sample Tables list, select CustomerID in the Sample Fields list, and click the > button to add the CustomerID field below the ContactID field.

7. Click Next to display the second Table Wizard dialog in which you select the name for your new table, and specify how to determine the table's primary-key field. Type CustContacts in the text box, and then select the No, I'll Set the Primary Key option (see Figure 5.13).

   Figure 5.13. The second Table Wizard dialog lets you rename the table and choose whether to let the Wizard or you specify the primary-key field.

   

8. Click Next to display the third dialog in which you select the primary-key field and its data type. Accept the ContactID and Consecutive Numbers... (AutoNumber) default values determined by the Table Wizard.

9. Click Next to open the Table Wizard's relationships dialog (see Figure 5.14). Scrolling the list shows that the Wizard hasn't detected a relationship with another table in the Northwind database.

   Figure 5.14. The Wizard doesn't find the related table (Customers) automatically in the fourth dialog.

   

   Tip

   In any wizard, you can always redo a step by clicking the Back button until you return to the step that you want to redo.

   Note

   The data type of the CustomerID field of the Contacts table is Long Integer and the CustomerID field of the Customers table is Text. So, the Wizard correctly doesn't automatically detect a relationship between these two fields. If the names and data types of the related fields are the same, the Wizard automatically detects the relationship.

10. Click Next to display the final Wizard dialog. Select the Modify the Table Design option (see Figure 5.15), and click the Finish button to display the new table in Design view.

    Figure 5.15. The last Table Wizard dialog offers the options of opening the table in Datasheet or Design view, or generating a data-entry form.

    

11. Select the Data Type cell of the CustomerID field, open the list, and select Text to match the data type of the CustomerID field of the Customers table.

12. In the General page of the properties pane, type 5 in the Field Size text box to match the length of the CustomerID field of the Customers table. Set the Required value to Yes and the Allow Zero length value to No, because CustomerID is a foreign key field and must contain a CustomerID value from the Customers table (see Figure 5.16).

    Figure 5.16. To establish a one-to-many relationship between the CustomerID fields of the Customers and CustContacts tables, the field data types must be the same (Text). Setting the Field Size property value to the same length as the primary key field of the related table is optional for text fields, but is a good database design practice. The Field size property for related Number fields must match.

    

13. After you finish reviewing the design of your new CustContacts table, close the table and save your design changes.

You can use the Table Wizard to create a table similar to CustContacts for supplier contacts (SuppContacts). In the preceding step 6, select the CustomerID field, click the Rename Field button to open the Rename Field dialog, and change the field name from CustomerID to SupplierID. The data type of the SupplierID field of Northwind's Suppliers table is Long Integer, so the Table Wizard automatically detects and adds the relationship between the SupplierID fields of the Suppliers and SuppContacts tables.

The Nwind05.mdb database in the \Seua11\Chaptr05 folder of the accompanying CD-ROM includes the CustContacts and SuppContacts tables, which you can import into Northwind.mdb.