APPENDIX A
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.
Exercises
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.
