Home

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:
     
    Employees
  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:
     
    Customers
    Customers
  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 

The Relationships Diagram

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 Relationships
  • If a table is currently displaying, on the Ribbon, click Table. In the Relationships section, click the Relationships button Relationships

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 Close. 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 Relationships. 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
     
    Relationships
  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
     
    Relationships
  22. On the Relationships section if the Ribbon, click the Close button Close
  23. When asked whether you want to save, click Yes
 
   
 

A Sub-Datasheet

 

Introduction

A sub-datasheet is a means of displaying the dependent records of a parent record on a datasheet. Obviously to make this possible, each record on a table must be configured to have child records. In other words, there must be a table containing a foreign key so that the child table can be, or has been, connected to the current table. For example, imagine that, in a hotel application, various customers have previously rented some rooms and sometimes you want to see the records related to a particular room.

Once a relationship has been established between records, when you open the parent table in Datasheet View, each record would appear with a + button to its left:

This means that Microsoft Access is configured to recognize relationships and apply them to show the sub-datasheet.

By its definition, a sub-datasheet allows you to view the related records by clicking the + button. This would expand that record and display its related records. You can expand just one record by clicking its + button or a few records but clicking the + buttons of the desired records. Here is an example:

Sub-Datasheet

You can also expand all records. To do this, open the table in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet -> Expand All

Sub-Datasheet

When a record is expanded, it displays a - button to its left. After viewing a record, to collapse it back, you can click its - button. You can do the same for any other record. You can also collapse all records. To do this, open the table in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet -> Collapse All.

Practical Learning: Introducing Subdatasheets

  1. To start a new database, press Ctrl + N
  2. Set the File Name of the database to Video Collection3 and click Create
  3. Close the default table without saving it
  4. To create a new table, on the Ribbon, click Create
  5. In the Tables section, click the Table Design
  6. Set the name of the first field to ActorID and set its Data Type to AutoNumber
  7. While the field is still selected, in the Tools section of the Ribbon, click the Primary Key button Primary Key
  8. Set its Caption to Actor ID
  9. In the upper section of the table, under ActorID, create a new field as follows:
    Field Name: Actor
    Data Type: Text
    Field Size: 50
  10. Set the last field as Notes with a Memo as Data Type
     
    Table
  11. To switch the table to the other view, on the Ribbon, click the View button View
  12. When you are asked whether you want to save the table, click Yes
  13. Type Actors and press Enter
  14. Press Enter and enter the following names of actors:
     
    Actor ID Actor
    1 Eddie Murphy
    2 Tom Cruise
    3 Ving Rhames
    4 Mel Gibson
    5 Charlie Sheen
    6 Jaime Pressly
    7 Sheryl Lee Ralph
    8 Daryl Hannah
    9 Joe Don Baker
    10 Victoria Rowell
    11 Grant Shaud
    12 Kevin McCarthy
    13 Charles S. Dutton
    14 Mia Kirshner
    15 Lane Smith
    16 Randy Quaid
    17 Philip Seymour Hoffman
    18 Delroy Lindo
    19 Rene Russo
    20 Gary Sinise
    21 Matthew Broderick
    22 Jean Reno
    23 Michael Douglas
  15. Close the table
  16. Create a new table in Design View with the following fields:
     
    Field Name Data Type Field Size Description
    DirectorID (Primary Key) AutoNumber   Automatic number
    Director Text 50 Name of a director such as "Mark Lynn" or directors as a group of such as "The Hughes Brothers"
    Notes Memo   Observations about the director or group of directors
  17. Save the table as Directors and switch to Datasheet View
  18. Enter some directors as follows:
     
    DirectorID Director
    1 Roland Emmerich
    2 Renny Harlin
    3 Tony Scott
    4 Ron Howard
    5 Sanjay Leela Bhansali
    6 Eddie Murphy
    7 J. J. Abrams
    8 Jonathan Lynn
    9 Mel Gibson
    10 Steven Spielberg
    11 Joel Gallen
    12 Oliver Stone
  19. Create a new table in Design View with the following fields:
     
    Field Name Data Type Field Size Caption
    GenreID (Primary Key) AutoNumber   Genre ID
    Genre Text 50  
    Notes Memo    
  20. Save the table as Genres and switch it to Datasheet View
  21. Enter a few categories on the table:
     
    Genre ID Genre
    1 Drama
    2 Comedy
    3 War
    4 Fitness
    5 Science Fiction
    6 Musical
    7 Adventure
    8 Documentary
    9 Fantasy
    10 Cartoon
    11 Biography
    12 Religious
    13 Hindu
    14 TV Show
  22. Close the table
  23. Create a new table in Design View with the following fields:
     
    Field Name Data Type Field Size Caption
    CategoryID (Primary Key) AutoNumber   Category ID
    Category Text 50  
    Notes Memo    
  24. Save the table as Genres and switch it to Datasheet View
  25. Enter a few categories on the table:
     
    Category ID Category
    1 General
    2 Police
    3 Politic
    4 Parody
    5 Environment
  26. Close the table

Creating a Sub-Datasheet

As we have seen so far, to have a relationship between two tables, you must create a primary key in one table and the corresponding foreign key in another table. You can then establish a relationship between both tables in the Relationships window. As stated already, Microsoft Access can take it upon itself to show the related records. You do not have to establish a relationship first in order to take advantage of the sub-datasheet effect. If you have created two tables, one with a primary key and another with a foreign key, you can create the sub-datasheet yourself.

To create a non-existing datasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More, position the mouse on Subdatasheet, and click Subdatasheet... This would open the Insert Subdatasheet dialog box. In the list of tables (or queries), you must click the table (or query) that has the foreign key that relates to the primary key of the current table. The names of the primary key and the foreign key would appear in the combo boxes. Once you click OK, Microsoft Access would take care of configuring the subdatasheet.

If a relationship has been established and a subdatasheet exists in a table but you do not want the subdatasheet to show anymore, you can remove it. To delete a subdatasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet, and click Remove.

Practical Learning: Creating a Sub-Datasheet

  1. Create a new table in Design View with the following fields:
     
    Field Name Data Type Caption Field Size
    VideoID (Primary Key) AutoNumber Video ID  
    Title Text   120
    DirectorID Number Director Long Integer
    CopyrightYear Number © Year Integer
    Length Number   Integer
    GenreID Number Genre Long Integer
    CategoryID Number Category Long Integer
    Rating Text   20
    Notes Memo    

    (To get the © character, you can open Microsoft Word, type (c) then select and copy it. Then paste it in the Caption property of the table field)

  2. Save the table as Videos and switch it to Datasheet View 
  3. Enter a few videos:
     
    Video ID Title Director © Year Length Genre Category Rating
    1 Distinguished Gentleman (The) 8   112 2 3 R
    2 Wall Street 12 1987 126 1   R
    3 Ransom 4   121 1   R
    4 Not Another Teen Movie 11 2005 100 2 4 Unrated
    5 Harlem Nights 6 1989 116 2   R
    6 M:i:III 7 2006 125 1 6 PG-13
    7 Devdas 5   175 13   Unrated
    8 Passion of the Christ (The) 9 2004   12   R
    9 Platoon 12 1986 120 3   R
    10 Day After Tomorrow (The) 1 2004 123 1 5 PG-13
    11 Beautiful Mind (A) 4 2001 135 1   PG-13
    12 Godzilla 1 1998 139 1 6 PG-13
  4. Save the table and close it
  5. From the Navigation Pane, double-click Directors: Table to open it
  6. On the Ribbon, click Home. In the Records section, click More -> Subdatasheet -> Subdatasheet...
  7. In the Insert Subdatasheet dialog box, make sure the Tables tab is selected. In the list box, click Videos and notice that DirectorID has been selected in the combo boxes:
     
    Insert Subdatasheet
  8. Click OK
     
  9. A message box information informs you and asks whether you want to create a relationship between both tables. Read it and click Yes
  10. Click a + button and notice the related records. Close the Directors table
     
    Videos
  11. When asked whether you want to save, click Yes
 
 
   
 

Previous Copyright © 2010-2012 FunctionX Next