Details on Data Relationships

The Diagram of a Relationship

Introduction

In the previous lesson, we saw how to establish a relationship between two tables. We also saw that the fields that relate both tables must be of the same data type. To make it possible to visually perform data entry on a table, we learned to create a lookup field. Indeed, this is based on Microsoft Access' high level of support for visual database development. In reality, the only important requirement is that the primary key and the foreign key be of the same data type. In most database scenarios and implementation, the primary key and the foreign key should integer based. As discussed in the previous lesson, the relationship is typically managed through the long integer data.

With experience, you do not need to create a lookup field on a table. Besides, since you will usually not let your users use your tables, your primary concern for user interact is to create the combo boxes on forms. To start, you can create the tables and the primary keys as you judge them necessary.

Practical Learning: Creating a Numeric Lookup Field

1. Start Microsoft Access
2. Create a Blank Database
3. Set the name of the database as Bethesda Car Rental2 and click Create
4. On the default table, click Click to Add and select Text
5. While Field1 is still selected, type Category and press Enter
6. Save the table as Categories and switch it to Design View
7. Complete the table as follows:
    

   Field Name	Data Type	Caption	Field Size	Format
   CategoryID (Primary Key)	AutoNumber	Category ID
   Category	Text		50
   Daily	Number		Double	Fixed
   Weekly	Number		Double	Fixed
   Monthly	Number		Double	Fixed
   Weekend	Number		Double	Fixed

8. Save the table and close it
9. On the Ribbon, click Create and, in the Forms section, click Form Design
10. Save the form as Rental Rates and change the following characteristics in the Property Sheet:
    Record Source: Categories
    Caption: Rental Rates
    Default View: Continuous Forms
    Navigation Buttons: No
11. Design the form as follows (don't try to match the fonts; use whatever font you have on your computer):
     
    
12. Save the form and switch it to Form View
13. Create the following records:
     

    Category	Daily	Weekly	Monthly	Weekend
    Economy	34.95	28.75	24.95	24.95
    Compact	38.95	32.75	28.95	28.95
    Standard	45.95	39.75	35.95	34.95
    Full Size	50.00	45.00	42.55	38.95
    Mini Van	55.00	50.00	44.95	42.95
    SUV	56.95	52.95	44.95	42.95
    Truck	62.95	52.75	46.95	44.95
    Van	69.95	64.75	52.75	49.95

14. Close the form
15. To create a new table, on the Ribbon, click Create and, in the Tables section, click the Table Design
16. Create the table with the following fields:
     

    Field Name	Data Type	Caption	Field Size
    CarID (Primary Key)	AutoNumber	Car ID
    TagNumber	Text	Tag Number	20
    Make	Text		50
    Model	Text		50
    CarYear	Number	Year	Integer
    CategoryID	Number	Category	Long Integer
    Doors	Number		Byte
    Picture	OLE Object
    Condition	Text		50
    Available	Yes/No
    Notes	Memo

17. Click Condition and, in the bottom section of the table, click the Lookup tab. Change the properties as follows:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "Excellent";"Good Shape";"Needs Repair";"Must be Retired"
    Bound Column: 1
    Column Count: 1
    Column Widths: 1"
    List Width: 1"
18. Save the table as Cars and close the table
19. Create a new table in Design View as follows:
     

    Field Name	Data Type	Caption	Field Size
    EmployeeID (Primary Key)	AutoNumber	Employee ID
    EmployeeNumber	Text	Employee #	20
    FirstName	Text	First Name	50
    LastName	Text	Last Name	50
    Title	Text		100
    Notes	Memo

20. Save the table as Employees and close it
21. Create a form for the Employees table, save it as Employees, and design it as you see fit. Here is an example:
     
22. Save the form and create the employees
23. Close the form
24. Create a new table in Design View with the following fields:
     

    Field Name	Data Type	Caption	Field Size
    CustomerID (Primary Key)	AutoNumber	Cutomer ID
    DrvLicNumber	Text	Driver's License #	50
    FullName	Text	Full Name	100
    Address	Text		100
    City	Text		50
    State	Text		100
    ZIPCode	Text	ZIP Code	20
    Notes	Memo

25. Save the table as Customers and close it
26. Create a form for the Customers table, save it as Customers, and design it as you see fit. Here is an example:
     
27. Save the form
28. Create some customers before closing the form
29. Create a new table in Design View with the following fields:
     

    Field Name	Data Type	Caption	Field Size	Other Properties
    RentalOrderID (Primary Key)	AutoNumber	Rental Order ID
    EmployeeID	Number	Processed By	Long Integer
    CustomerID	Number	Processed For	Long Integer
    CarID	Number	Car	Long Integer
    CarCondition	Text	Car Condition	50
    TankLevel	Text	Tank Level	50
    MileageStart	Number	Mileage Start	Integer
    MileageEnd	Number	Mileage End	Integer
    TotalMileage	Number	Total Mileage	Integer
    StartDate	Date/Time	Start Date
    EndDate	Date/Time	End Date
    TotalDays	Number	Total Days	Integer
    RateApplied	Number	Rate Applied	Double	Format: Fixed
    TaxRate	Number	Tax Rate	Double	Format: Percent Default Value: 7.50
    OrderStatus	Text	Order Status	50	Default Value: "Unknown"
    Notes	Memo

30. Click TankLevel and, in the bottom section of the table, click the Lookup tab. Change the properties as follows:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "Empty";"1/4 Empty";"Half";"3/4 Full";"Full"
    Bound Column: 1
    Column Count: 1
    Column Widths: 1"
    List Width: 1"
31. Click OrderStatus and, in the bottom section of the table, click the Lookup tab. Change the properties as follows:
    Display Control: Combo Box
    Row Source Type: Value List
    Row Source: "Unknown";"Car On Road";"Order Completed";"Order Reserved"
    Bound Column: 1
    Column Count: 1
    Column Widths: 1"
    List Width: 1"
32. Save the table as RentalOrders
33. Close it 

Tables and fields relationships can be created and managed in a special window called the Relationships window. To display it:

• If no table is currently opened, on the Ribbon, click Database Tools. In the Show/Hide section, click the Relationships button
• If a table is currently displaying, on the Ribbon, click Table. In the Relationships section, click the Relationships button

After clicking one of those:

• If no relationship exists among the tables in the current database, the Show Table dialog box would come up, asking you to select the tables whose relationship(s) you want to create. To add a table, select it, click Add and click Close
• If at least one relationship has been created between two tables, the Relationships window would come up and display that relationship or the already existing relationships

When you are working on the relationships of your tables, a window with a tab labeled Relationships displays in Microsoft Access. Also, the Ribbon is equipped with a tab labeled design and that includes two sections:

The Design tab of the Ribbon provides various tools to assist you with creating and managing the relationships. For example, if the Show Table dialog box has been closed and if you want to show it:

• You can click the Show Table button on the Ribbon
• You can right-click the body of the Relationships window and click Show Table...

You can create a relationship only between two tables that are present on the Relationships window. This means that even if a table is part of your database and you want to link it to another table (of your database), if the table has not been added to the Relationships window, you cannot create or manage its relationship to another table. Of course, there are other ways you can create relationships without using the Relationships window but the Relationships window gives you detailed means of creating and managing relationships.

To establish a relationship that does not yet exist between two tables, you can drag the primary key from the parent table to the foreign key of the desired table. If you drag and drop accurately, the relationship would be acknowledged and you can just click Create to make it formal. If you dropped the primary key on the wrong field, you would have time to select the appropriate fields in the Edit Relationship dialog box.

After working with the Relationships window, you can close it by clicking its Close button . You would be asked to save it in order to keep the relationship(s) created.

Practical Learning: Establishing Tables Relationships

1. The Bethesda Car Renatal2 database should still be opened.
   On the Ribbon, click Database Tools
2. In the Show/Hide section, click the Relationships button . The Show Table property sheet comes up:
    
   
3. Click Categories
4. Click the Add button 
5. Double-click Cars
6. On the Show Table dialog box, click the Close button
7. To add another table as if we forgot it, right-click an empty area in the Relationships window and click Show Table...
8. In the Show Table dialog box, click Customers
9. Press and hold Shit
10. Click RentalOrders and release Shift
11. Click Add and click Close
12. Drag the CategoryID field from the Categories table and drop it on top of the CategoryID field in the Cars table:
     
    
     
    The Edit Relationship dialog box would come up
     
    
13. Click Create
14. Now you have a line relating these two tables.
    Drag any field from the Cars table and drop it on top of any field in the RentalOrders table if you missed the target
     
    
15. Once again, the Edit Relationship dialog box comes up.
    On the dialog box, under the left Table/Query, click the field that is selected to display its combo box. In that left combo box, select CarID and press Tab
16. Under Related Table/Query, click the field that is selected. Click the arrow of its combo box and select CarID
     
     
17. Click Create
18. In the same way, drag EmployeeID from the Employees table and drop it on EmployeeID in the RentalOrders table
19. Click Create
20. Drag CustomerID from the Customers table and drop it on CustomerID in the RentalOrders table
21. Click Create
     
    
22. On the Relationships section if the Ribbon, click the Close button
23. When asked whether you want to save, click Yes