Appendix A: Forms Involving Multiple Tables

Ron McFadyen

In Chapter 3, we created simple forms for single tables. A very useful form is one where the user can interact with data that comes from more than one table. We will consider how this can be done in cases where two tables are related by a one-to-many relationship.

We will illustrate creating such a form using the Microsoft Access Form Wizard. As you will see, the Form Wizard will create a form and a subform. These two forms will have a connection established based on related fields: a primary key and a foreign key.

Consider using the Company database:

    1. If the one-to-many relationship between Department table and Employee table does not exist, then create this now. Note that this is Exercise 1 in Chapter 5. After doing this you should have the relationship as shown:

Department and Employee Relationship Diagram

    1. Use the Create tab and create a form using the Form Wizard. Select all fields from the Department table:

Form Wizard

    1. Do not click Next or Finish, instead choose the Employee table and select all of its fields and now the Selected Fields component shows fields from both tables:

Form Wizard - Selecting fields from Employee and Department tables

    1. Now, click Next and Microsoft Access asks you how the data should be viewed:

Form Wizard - Step on how to view the form data.

    1. We want the data displayed “by Department” and we want MS Access to use “Form with subform(s)” so you can select Next and Microsoft Access will let you choose a layout. Choose Datasheet Layout. Click Next and Access will ask you to name the form – name the form EmployeesByDepartment and name the subform EmployeesSubform:

Form Wizard: naming form and subform

    1. Click Finish. Microsoft Access will display the finished form called EmployeesByDepartment – see below. Experiment with the form: notice the two sets of navigation buttons – one that controls the department being viewed, and the other that controls the view of the department’s employees.

Exercises

  1. Consider using the University database. Create a form to allow a user to view courses by department.
  2. Consider using the Library database. There are two one-to-many relationships. Create a form to list the loan records for a book. Create another form to list the loan records for a member.
  3. Consider using the Orders database. This database has several one-to-many relationships. Create appropriate forms to list
    1. A customer and the customer’s orders;
    2. An order and its detail lines;
    3. A product and the order detail lines where the product is referenced;
    4. A category and the products belonging to the category.

License

Share This Book