DBC 3.5 Access Workbench Section02

Database Concepts 3.5 David M. Kroenke and David J. Auer

THE ACCESS WORKBENCH Section 2

Working with Multiple Tables in Microsoft Access

Kroenke and Auer

Database Concepts 3.5 – The Access Workbench Section 2

In Section 1 of “The Access Workbench,” we learned to create Microsoft Access databases, tables, forms, and reports. However, we we limited ourselves to working with one table. table. In this section, we will cover the following objectives: Illustrate the modification problems that have been discussed in Chapters 1 and 2. Learn how to work with multiple tables. We will continue to use the WMCRM database database we created in Section 1. At this point, we have created and populated populated (which means means we’ve inserted the data) the CONTACT table. table. Figure AW -

2-1 shows the actual actual contacts that have been made with each customer. Note that there is no customer with CustomerID CustomerID number 2. This is because we deleted and reentered the data for Jessica Christman.

Figure AW-2-1 — CONTACT Data

Kroenke and Auer


Possible Modification Problems in the WMCRM Database Now, we know from the topics covered in this chapter that we really need a separate table to store this data, but in order to illustrate modification problems in action, let’s combine it in one

table with the data already in CUSTOMER. This has been done, and is available in the file WMCRM-Combined-Data.accdb, which is available at the Web site for this book (http://www.prenhall.com/kroenke). Start Access and then open this file. Let’s take a look at the WMCRM-Combined-Data database. Opening an Existing Access Database 1.

Click Start | All Programs | Microsoft Office | Microsoft Access 2007 . The Microsoft Access 2007 window appears.

NOTE: The menu command or icon location used to start Microsoft Excel 2007 may vary depending both on the operating system you are using and how Microsoft Office is installed on your computer. 2.

Click the Microsoft Office Button to display the File Menu, and then click the Open button as shown in Figure AW-2-2.

Click the File Menu Open button

Figure AW-2-2 — The File Menu Open Button

Kroenke and Auer

3.


The Open dialog box is displayed, as shown in Figure AW-2-3. Browse to the WMCRMCombined-Data.accdb file, click the file name to highlight it, and then click the Open button.

Click the file name to select the file Click the Open button

Figure AW-2-3 — The Open Dialog Box 4.

The Security Warning bar appears with the database. Click the Security Warning Options… button to display Microsoft Office Security Options dialog box.

5.

Click the “Enable this content” radio button to select this option, and then click the OK button.

6.

In the Navigation Pane, double-click the CUSTOMER_CONTACT table object to open it.

7.

Click the Shutter Bar Open/Close Button to compress the Navigation Pane.

8.

The CUSTOMER_CONTACT table appears in Datasheet view as shown in Figure AW -2-4. Note that there is one line for e ach contact, which has resulted in the duplication of basic customer data. For example, there are five sets of basic data for Ben Griffey.

Figure AW-2-4 — The CUSTOMER_CONTACT Table 9.

Close the CUSTOMER_CONTACT table by clicking the Document window Close button.

10.

Click the Shutter Bar Open/Close Button to expand the Navigation Pane.

Kroenke and Auer

11.


In the Navigation Pane, double-click the Customer Contact Data Input Form object to open it. The Customer Contact Data Input Form appears as shown in Figure AW-2-5. Note that the form displays all the data for one re cord in the CUSTOMER_CONTACT table.

All fields from the CUSTOMER_ CONTACT table appear on the form Form browsing buttons

Figure AW-2-5 — The Customer Contact Data Input Form 12.

Close the Customer-Contact Data Input form by clicking the Document window Close button.

13.

In the Navigation Pane, double-click the Wallingford Motors Customer Contact Report report to open it.

14.


15.

The Wallingford Motors Customer Contact Reports appears as shown in Figure AW-2-6 on the next page. Note that the form displays the data for all contacts in the CUSTOMER_CONTACT table, sorted by CustomerNumber and Date. For example, all the contact data for Ben Griffey (who has a CustomerID of 1) is grouped at the beginning of the report.

Kroenke and Auer


All contact data for each customer is grouped together and sorted by date

Figure AW-2-6 — The Wallingford Motors Customer Contact Report 16.

Close the Wallingford Motors Customer Contact Report by clicking the Document window Close button.

17.


Now, let’s assume that Ben Griffey has changed hi s email address from

[email protected] to [email protected]. In a well-formed relation, we would only have to make this change once, but a quick examination of Figures AW-2-4 through AW-26 shows that Be n Griffey’s email address appears in multiple records. This means that we have to change it in every record to avoid an update problem. Unfortunately, it is easy to miss one or more records, especially in large tables. Updating Ben Griffey’s Email Address 1.

In the Navigation Pane, double-click the Customer Contact Data Input Form object to open it. Since Ben Griffey is the customer in the first record, his data is already in the form.

2.

Edit the Email address to read [email protected] as shown in Figure AW-2-7 on the next page.

Kroenke and Auer


The Email address has been updated The Next record button

Figure AW-2-7 — The Customer-Contact Data Input Form with the Updated Email Address 3.

Click the Next Record button to move to the next record in the table. Again, the record shows Ben Griffey’s data, so again edit the Email address to read [email protected].

4.

Click the Next Record button to move to the next record in the table. For the third time, the record shows Ben Griffey’s data, s o again edit the Email address to read [email protected].

5.

Click the Next Record button to move to the next record in the table. For the fourth time, the record shows Ben Griffey’s data, so again edit the Email address to read [email protected].

6.

Click the Next Record button to move to the next record in the table. Finally, another customer’s data appears in the form, so we will assume that we’ve made all the necessary updates to the database records.

7.

Close the Customer-Contact Data Input form by clicking the Document window Close button

8.

In the Navigation Pane, double-click the Wallingford Motors Customer Contact Report report to open it.

9.


10.

The Wallingford Motors Customer Contact Report now appears as shown in Figure AW2-8. Note that the email addresses shown for Ben Griffey are inconsistent—we missed

Kroenke and Auer


one record when we updated the table, and now we have inconsistent data. A modification error—in this case an update error―has occurred. A modification problem has occurred. Not all records were updated with the new email address, and the database records are now inconsistent

Figure AW-2-8 — The Updated Wallingford Motors Customer Contact Report 11.

Close the Wallingford Motors Customer Contact Report by clicking the Document window Close button

12.


This simple example shows how easily modification problems can occur in tables that are not normalized. With a set of well-formed, normalized tables, this problem would never have occurred. Closing the WMCRM-Combined-Data Database 1.

Click the Close button. The database is closed and the Access program is exited.

Working with Multiple Tables The table structure for the CUSTOMER_CONTACT table in the WMCRM-Combined-Data database is:

Kroenke and Auer


CUSTOMER_CONTACT (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Fax, Email, Date, Type, Remarks)

Applying the normalization process discussed in this chapter, we will have the following set of tables and referential integrity constraint: CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Fax, Email)

CONTACT (ContactID, CustomerID , Data, Type, Remarks)

CustomerID in CONTACT must exist in CustomerID in CUSTOMER

Our task now is to build and populate the CONTACT table, and then to e stablish the relationship and referential integrity constraint between the two tables. First, we need to create and populate (insert data into) the CO NTACT table, which will contain the columns shown in the table in Figure AW-2-9. As before, the table shows the column name and characteristics for each column.

1

Figure AW-2-9 — Database Column Characteristics for the CONTACT Table

1

Although we are using it for simplicity in this example, a column, such as Remarks, can cause problems in a database. For a complete discussion, see David M. Kroenke, Database Processing: Fundamentals, Design, and Implementation 10th Edition. (Upper Saddle River, NJ: Prentice Hall, 2006, pages 112-113.)

Kroenke and Auer


The CustomerID column appears again in CONTACT, this time designated as a foreign key. As discussed in this chapter, the term foreign key designates this column as the link to the CUSTOMER table. The value in the CustomerID column of CONTACT tells us which customer was contacted―all we have to do is look up the value of CustomerID in the CUSTOMER tab le. Note that when we build the CONTACT table itself, there is no “foreign key” setting . We will

see how to set up the actual database relationship between CUSTOMER and CONTACT after we’ve completed building the CONTACT table.

Other things to note are: Some new data types are being used —Number, Data/Time, and Memo. CustomerID must be set as a Number and specifically as a Long Integer to match the data type Access creates for the AutoNumber data type in the CUSTOMER table. The Type column has only four allowed values —Phone, Fax, Email, and Meeting. For now, we will simply input only these data values. In “The Access Workbench,” we will learn how to enforce this data restriction for this column.

Creating the CONTACT table 1.

Click Start | All Programs | Microsoft Office | Microsoft Access 2007. The Microsoft Access 2007 window appears.

2.

In the list of database files in the Open Recent Database pane, click WMCRM.accdb file name.

3.

The database file opens in Access, and the Security Warning bar appears with the database. Click the Security Warning Options… button to display Microsoft Office Security Options dialog box.

4.

Click the “Enable this content” radio button to select this option, and then click the OK utton.

5.

Click the Create command tab to display the Create Ribbon.

6.

Click the Table Design button.

7.

The Table1 tabbed document window is displayed in Design view. Note that along with the Table1 window, a contextual tab named Table Tools is displayed, and that this tab adds a new command tab and ribbon named Design to the set of command tabs displayed.

8.

Using the steps we followed to create the CUSTOMER table in Section 1, you should be able to create most of the CONTACT table. The steps below will detail only new information that you need to know to complete the CO NTACT table.

Kroenke and Auer


9.

When creating the ContactID column, be sure to set this column as the primary key.

10.

When creating the CustomerID column, set the data type to Number . Note that the default Field Size for Number is Long Integer so that no change is necessary. Be sure to set the Required property to Yes.

11.

When creating the Date column, as soon as you en ter the column name and try to move to the Data Type column, Access displays a dialog box warning you that Date is a reserved word as shown in Figure AW-2-10. Click the OK button

Click the OK button

Figure AW-2-10 — The Reserved Word Warning

NOTE: Normally, we should avoid reserved words such as Date and Time, but it shouldn’t matter in this context. Generally, column names such as ContactDate are preferred, both to avoid reserved words and to clarify exactly which date is referenced. 12.

When creating the Date column, set the data type to Date/Time, and set the Format to Short Date as shown in Figure AW-2-11 on the next page. Be sure to set the Required property to Yes.

13.

To name and save the CONTACT table, click the Save button in the Quick Access Toolbar.

14.

Type the table name CONTACT into the Save As dialog box text box, then click OK. The table is named and saved, and now appears with the table name CONTACT.

15.

To close the CONTACT table, click the Close button in the upper right corner of the tabbed document window. The CONTACT table now appears as a table object in the Navigation Pane.

Creating Relationships Between Tables In Access, relationships between tables are built in the Relationships window. The Relationships window is accessed by using the Tools | Re lationships… command on the Access

main menu. Creating the Relationship Between the CUSTOMER and CONTACT Tables 1.

Click the Database Tools command tab to display the Database Tools Ribbon, as shown in Figure AW-2-12 on the next page.

Kroenke and Auer


Select the Short Date date format from the dropdown list

Figure AW-2-11 — Setting the Date Format

The Database Tools command tab and Ribbon The Relationships button The Show/Hide group

Figure AW-2-12 — The Database Tools Ribbon 2.

Click the Relationships button in the Show/Hide group. As shown in Figure AW-2-13 on the next page, the Relationships tabbed document window appears together with the Show Table dialog box. Note that along with the Relationships window, a contextual tab named Relationship Tools is displayed, and that this tab adds a new command tab and ribbon named Design to the set of command tabs displayed.

Kroenke and Auer


The Relationship Tools contextual command tab The Design command tab and Ribbon The Relationships tabbed document window The Show Table dialog box Select a table name, then click the Add button to add the table to the Relationships window

Figure AW-2-13 — The Relationships Window

3.

In the Show Table dialog box, the CONTACT table is already selected. Click the Add button to add CONTACT to the Relationships window.

4.

In the Show Table dialog box, click the CUSTOMER table to select it. Click the Add button to add CUSTOMER to the Relationships window.

5.

In the Show Table dialog box, click the Close button to close the dialog box.

6.

You can rearrange and resize the table objects in the Relationships window using standard Windows drag-and-drop techniques. Rearrange the CUSTOMER and CONTACT table objects until they appear as shown in Figure AW-2-14 on the next page. Now we are ready to create the relationship between the tables.

NOTE: A formal description of how to create a relationship between two tables is “In the Relationships window, drag a primary key column and drop it on top of the corresponding foreign key column .” It is easier to understand this after you have actually done it. 7.

Click and hold the column name CustomerID in the CUSTOMER table object, and then drag it over the column name CustomerID in the CONTACT table . The Edit Relationships dialog box appears as shown in Figure AW-2-15 on the next page.

Kroenke and Auer


The table objects have been enlarged and rearranged into the arrangement shown here Click, drag and drop the CUSTOMER CustomerID field onto the CONTACT CustomerID field

Figure AW-2-14 — The Updated Wallingford Motors Customer Contact Report

The Edit Relationships dialog box Click the Enforce Referential Integrity check box, and then click the Create button to create the relationship

Figure AW-2-15 — The Updated Wallingford Motors Customer Contact Report

NOTE: In CUSTOMER, CustomerID is the primary key, while in CONTACT, CustomerID is the foreign key. 8.

Click the Enforce Referential Integrity check box.

9.

Click the Create button to create the relationship between CUSTOMER and CONTACT. The relationship between the tables now appears in the Relationships window as shown in Figure AW-2-16 on the next page.

Kroenke and Auer

The new relationship now appears in the Relationships window diagram—note that the line connects the related fields

10.


Figure AW-2-16 — The Completed Relationship

To close the Relationships window, click Close button in the upper right corner of the document window. An Access dialog box appears asking “Do you want to save changes to the layout of Relationships?” Click the Yes button to save the changes and close the window.

At this point, we need to add data on customer contacts to the CONTACT table. Using the CONTACT table in Datasheet View as we’ve already discussed, enter the data shown in Figure

AW-2-1 into the CONTACT table. Note that there is no customer with CustomerID number 2. This is because we deleted and reentered the dat a for Jessica Christman in “The Access Workbench: Section 1.” The CONTACT table with the data inserted appears as shown in Figure

AW-2-17. Be sure to close the table after the data is entered.

Figure AW-2-17 — Data in the CONTACT Table

Kroenke and Auer


Using a Form that Includes Two Tables In Section 1, we created a data entry form for the CUSTOMER table. Now let’s create an Access

form that will let us work with the combined data from both tables. Creating a Form for Both the CUSTOMER and CONTACT Tables 1.


2.

Click the More Forms gallery arrow button to display the More Forms gallery.

3.

Click the Form Wizard button in the More Forms gallery. The Form Wizard appears.

4.

Select the CUSTOMER table in the Tables/Queries drop-down list. We will add all of the columns, so click the right-facing double chevron button to add all the columns, but do NOT click the Next > button yet!

5.

Select the CONTACT table in the Tables/Queries drop-down list. Individually select and add the Date, Type, and Remarks columns to the Selected Fields list using the rightfacing chevron button. Now, click the Next > button.

NOTE: We have just created a set of columns from two tables that we want to appear on one form. 6.

We are now asked, “How do you want to view your data?” We will use the default by CUSTOMER selection as we want to see all contacts for each customer. We will also use the selected Forms with subforms option, which will treat the CONTACT data as a subform within the CUSTOMER form. Click the Next > button.

7.

We are now asked, “What layout would you like for your subform?” We will use the default Datasheet layout, so click the Next > button.

8.

We are now asked, “What style would you like?” We will use the default Access 2007 style, so click the Next > button.

9.

We are now asked, “What titles do you want for your form?” Type the form tile WMCRM Customer Contacts Form in the Form: text box, and Type the form title Contact Data in the Subform: text box. Click the Finish button. The completed form appears.

10.

Click the Shutter Bar Open/Close Button to compress the Navigation Pane. The completed form is displayed as shown in Figure AW -2-18 on the next page.

11.


12.

Close the form window.

Kroenke and Auer


Buttons for scrolling through the CONTACT records for this customer Buttons for scrolling through the CUSTOMER records

Figure AW-2-18 — The Completed Form for CUSTOMER and CONTACT Data

Creating a Report that Includes Data from Two Tables We can also create reports that include data from two o r more tables. Let’s create an Access report that will let us use the combined data from both the CUSTOMER and CONTACT tables. Creating a Report for Both the CUSTOMER and CONTACT Tables 1.


2.

Click the Report Wizard button to display the Report Wizard.

3.

Select the CUSTOMER table in the Tables/Queries drop-down list. We will use only the following columns: LastName, FirstName, Phone, Fax, and Email. Click each column name to select it, then click the right-facing chevron button to add each column to the Selected Fields, but do not click the Next > button yet!

4.

Select the CONTACT table in the Tables/Queries drop-down list. Individually select and add the Date, Type, and Remarks columns to the Selected Fields list using the rightfacing chevron button. Now, click the Next > button.

5.

We are now asked, “How do you want to view your data?” We will use the default by CUSTOMER selection as we want to see all contacts for each customer. Click the Next> button.

6.

We are now asked, “Do you want to add any grouping levels?” We’ll use the default nongrouped column listing, so click the Next> button.

Kroenke and Auer


7.

We are now asked, “What sort order do you want for detail records?” This is the sort order for the CONTACT information. The most useful sorting order is by Date in ascending order. Click the sort field 1 drop-down list arrow and select Date. Leave the sort order button on its current setting of Ascending. Click the Next button.

8.

We are now asked, “How would you like to lay out your report?” We will use the default setting of stepped layout, but click the Landscape orientation radio button to change the report orientation to landscape. Then click the Next> button.

9.

Now we are asked, “What style would you like?” We will use the default setting of Office. Then click the Next> button.

10.

Finally we are asked, “What title do you want for your report?” Edit the report title to read Wallingford Motors Customer Contacts Report . Leave the Preview the report radio button selected. Click the Finish button. The completed report is displayed.

11.

Click the Shutter Bar Open/Close Button to compress the Navigation Pane. The completed report is displayed as shown in Figure AW-2-19.

View gallery arrow button Design View button There are column width problems in the report—the Phone and Fax data are not completely displayed

Figure AW-2-19 — The Wallingford Motors Customer Contacts Report 12.

Unfortunately, the Access form wizard made various areas on the report too small. We will use report Design View to fix this. Click the View Gallery arrow button as shown in Figure AW-2-19 to display the View Gallery.

13.

In the View Gallery, click the Design View button. The report is displayed in report Design view as shown in Figure AW-2-20 on the next page.

Kroenke and Auer


Report View button Report Design Tools contextual command tab Design command tab and Ribbon The report in report Design view Select a column in the report, and then adjust the column width by dragging the edge of the column

Figure AW-2-20 — Report Design View 14.

We can adjust report column widths by selecting the column and then using standard drag-and-drop techniques to move the column boundaries. Adjust the column widths of the LastName, FirstName, Phone, Fax, and Email field as needed. To see the effects of your changes, switch between Report view and Design view. When you have made all the necessary changes, return to Report view. The final report with the adjusted Date fields is shown in Figure AW-2-21 on the next page.

15.

Click the Save button to save the changes to the report design.

16.


17.

Click the document window Close button to close the report window.

Closing the Database and Exiting Access That completes the work we’ll do in this section of “The Access Workbench.” As usual, we will

finish by closing the database and Access. Closing the WMCRM Database and Exiting Access 1.

To close only the WMCRM : Database and exit Access 2007, click the Close button in the upper right corner of the Access 2007 Window.

Kroenke and Auer


Data for each customer

List of contact data for each customer The column width problems have been fixed—the Phone and Fax data are displayed correctly

Figure AW-2-21 — The Completed Wallingford Motors Customer Contacts Report

Kroenke and Auer


Access Workbench Exercises AW.2.1

In the “Access Workshop Exercises” in Chapter 1, we created a database for the

Wedgewood Pacific Corporation (WPC) of Seattle, Washington, and created and populated the EMPLOYEE table. In this exercise, we will build the rest of the tables needed for the database, create the referential integrity constraints between them, and populate the tables. The full set of normalized tables for the WPC database is as follows: DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)

EMPLOYEE (EmployeeNumber, FirstName, LastName, Department , Phone, Email)

PROJECT (ProjectID, Name, Department , MaxHours, StartDate, EndDate)

ASSIGNMENT ( ProjectID , EmployeeNumber , HoursWorked)

The primary key of DEPARTMENT is DepartmentName, the primary key of EMPLOYEE is EmployeeNumber, and the primary key of PROJECT is ProjectID. Note that the EMPLOYEE table is the same as the table we have created, except that Department is now a foreign key. In EMPLOYEE and PROJECT, Department is a foreign key referencing DepartmentName in DEPARTMENT. Note that a foreign key does not need to have the same name as the primary key to which it refers. The primary key of ASSIGNMENT is the composite (ProjectID, EmployeeNumber). ProjectID is also a foreign key referencing ProjectID in PROJECT, and EmployeeNumber is a foreign key referencing EmployeeNumber in EMPLOYEE. The referential integrity constraints are: Department in EMPLOYEE must exist in DepartmentName in DEPARTMENT

Kroenke and Auer


Department in PROJECT must exist in DepartmentName in DEPARTMENT

ProjectID in ASSIGNMENT must exist in ProjectID in PROJECT

EmpNumber in ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE

A.

Figure 2-22 shows the column characteristics for the WPC DEPARTMENT table. Using the column characteristics, create the DEPARTMENT table in the WPC.accdb database.

Figure 2-22 — Column Characteristics for the DEPARTMENT Table B.

Create a data input form for the DEPARTMENT table named WPC Department Data Form. Make any adjustments necessary to the form so that all data displays properly. Use this form to enter the data in the DEPARTMENT table shown in Figure 2-23 into your DEPARTMENT table.

Figure 2-23 — WPC DEPARTMENT Data

Kroenke and Auer

C.


Create the relationship and referential integrity constraint between DEPARTMENT and EMPLOYEE.

D.

Figure 2-24 shows the column characteristics for the WPC PROJECT table. Using the column characteristics, create the PROJECT table in the WPC.accdb database.

Figure 2-24 — Column Characteristics for the PROJECT Table E.

Create the relationship and referential integrity constraint between DEPARTMENT and PROJECT.

F.

Create a data input form for the PROJECT table named WPC Project Data Form. Make any adjustments necessary to the form so that all data

displays properly. Use this form to enter the data in the PROJECT table shown in Figure 2-25 into your PROJECT table.

Figure 2-25 — WPC PROJECT Data

Kroenke and Auer

G.


When creating and populating the DPARTMENT table, the data was entered into the table before the referential integrity constraint with EMPLOYEE was created, but when creating and populating the PROJECT table, the referential integrity constraint was created before entering the data. Why did the order of the steps differ? Which order is normally the correct order to use?

H.

Figure 2-26 shows the column characteristics for the WPC ASSIGNMENT table. Using the column characteristics, create the ASSIGNMENT table in the WPC.accdb database.

Figure 2-26 — Column Characteristics for the ASSIGNMENT Table I.

Create the relationship and referential integrity constraint between ASSIGNMENT and PROJECT, and between ASSIGNMENT and EMPLOYEE.

J.

Create a data input form for the ASSIGNMENT table named WPC Assignment Data Form. Make any adjustments necessary to the form so that all data displays properly. Use this form to enter the data in the ASSIGNMENT table shown in Figure 2-27 on the next page into your ASSIGNMENT table.

Kroenke and Auer

K.


Create a data input form for the both the DEPARTMENT and EMPLOYEE tables named WPC Department Employee Data Form . This form should show all the employees in each department.

Figure 2-27 — WPC ASSIGNMENT Data L.

Create a report named Wedgewood Pacific Corporation Department Employee Report that presents the data contained in your DEPARTMENT

and EMPLOYEE tables. The report should group employees by department. Print out a copy of this report.

DBC 3.5 Access Workbench Section02

Recommend Documents