Forms Involving Multiple Tables

Consider the Company database:

  • If the one-to-many relationship between Department and Employee 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:


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


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


  • Now, click Next and MS Access asks you how the data should be viewed:


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


  • Click Finish. MS 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.



1) Consider the University database. Create a form to allow a user to view courses by department.


2) Consider 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 the Orders database. This database has several one-to-many relationships. Create appropriate forms to list:

  • a customer and the customer’s orders;
  • an order and its detail lines;
  • a product and the order detail lines where the product is referenced;
  • a category and the products belonging to the category.


Share This Book