Mastering Access 2003: One-to-Many Relationships Forms

1. Exploring MicrosoftAccess 2003 Chapter 5 One-to-Many Relationships: Forms - Subforms and Multiple Table Queries

2. Objectives (1 of 2) • One-to-many relationship essential in database design • Use the Relationship window • Define referential integrity • Distinguish between a main form and a subform

3. Objectives (2 of 2) • Create a query based on multiple tables • Create a main form with two subforms

4. Overview • One-to-many relationships between tables isthe real power of Access • Primary key of one table is foreign key of therelated table • Referential integrity ensures consistency between tables • Main forms and subforms created from relatedtables. • Reports based on a multiple-table query

5. Case StudyConsumer Loans • Understanding the need for a relational database • One-to-many relationship – use Relationships window • Need a primary key, a unique identifier • Foreign key in the ‘many’ table is the primary key in the ‘one’ table • Enforce referential integrity

6. Relationships Window ConsultantID is primary key in Consultant table Relationship line ConsultantID is foreign key in Clients table

7. Referential Integrity Delete Record button Click + to display related records You cannot delete a Consultant without first deleting related Clients

8. One-to-Many Relationships Hands-On Exercise 1 • Open the National Bank Database • Create and Delete Relationships • Add a Customer Record • Add a Loan Record • Referential Integrity • Close the Database

9. Forms Used as interfaces over tables or queries different components: - bound controls - unbound controls - calculated controls - drop-down list box (combo box) - check box - option group - command buttons - list box

12. Controls can be bound, unbound, or calculated: • Bound control  A control whose source of data is a field in a table or query is called a bound control. You use bound controls to display values that come from fields in your database. The values can be text, dates, numbers, Yes/No values, pictures, or graphs. For example, a text box that displays an employee's last name might get this information from the Last Name field in the Employees table. • Unbound control  A control that doesn't have a source of data (such as a field or expression) is called an unbound control. You use unbound controls to display information, pictures, lines or rectangles. For example, a label that displays the title of a form is an unbound control.

13. Calculated control  A control whose source of data is an expression, rather than a field, is called a calculated control. You specify the value that you want to use as the source of data in the control by defining an expression. An expression can be a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values. For example, the following expression calculates the price of an item with a 25 percent discount by multiplying the value in the Unit Price field by a constant value (0.75). =[Unit Price] * 0.75

14. Subforms

15. Subforms are generated using Wizard

16. Subforms • Subform is a form within a form • Main form would be based on primary table while subform is based on related table • Use Subform Wizard • Can be created either on main form or dragged from a separate form

17. Subform Consultant C01 Client related to Consultant C01 Status bar for Clients form Status bar for Consultants form

18. Creating a Subform Hands-On Exercise 2 • Create Customers Form using Form Wizard • Modify Customers Form • Create Loans Subform • View Customers Form • Add Payment Amount • Enter a New Loan • Print the Form

19. Multiple Table Queries • Query information from two tables • Create a select query using both tables • Query window displays selected fieldsof each table

20. Multiple Table Queries Run button Column selector Select table Enter criteria

21. Queries and Reports Hands-On Exercise 3 • Create a Select Query • Move and Size the Field Lists • Create and Run the Query • Create and Print a Report

22. Expanding the Database • Can expand a database without disturbing existing tables • Use relationship window to establishhow fields from specific tables will berelated • Can use multiple subforms

23. Linked Subforms Hands-On Exercise 4 • Add a relationship • Create the Payments Subform • Change the Default View • The Loans Subform in Form View • The Customers Form • The Finishing Touches • Make Your Payments

25. Chapter 5 Summary (1 of 2) • Access database may contain multiple tables • Each table has a primary key which is theforeign key of a related table • Relationships window to create one-to-manyrelationships • Referential integrity ensures consistencybetween related tables

26. Chapter 5 Summary (2 of 2) • Subform is a form within a form • A main form can have multiple subforms • Select query can include fields from several tables • Tables can be added at any time withoutdisturbing existing data

27. Practice with Access (1 of 2) 1. Adding Clip Art 2. The Report Wizard 3. Employees by Location 4. Employees by Title

28. Practice with Access (2 of 2) 5. The Switchboard 6. The Table Analyzer Wizard 7. Synchronizing Reports 8. Data Access Pages

29. Case Studies • Recreational Sports League • The Personnel Director • The Franchise • Widgets of America