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. 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:

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

1. 3. 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:

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

1. 5. 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:

1. 6. 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.