Home

Introduction to Data Relationships

The Keys to a Relationship

Introduction to Relationships

A relational database is a computer application that contains two or more tables so that information in one table is made available to another table or other tables that need(s) it. The information is entered once in one particular table. If the same information is needed in another table, it is simply identified one way or another. This reduces, and can eliminate, the likelihood of mistakes that result from duplicate data.

A Primary Key

To create a relational database, you must have a way for tables to communicate or relate to each other. To start, for a table to make its information available to the other table(s), the table must have a way to be identified. This is done by creating a field used to refer to that table. This field is called a primary key. The primary key can be represented by one field or it can be a combination of fields.

To make a field a primary key, display its table in the Design View. You have two options:

  • You can right-click the field and click Primary Key
  • In the Tools section of the Design tab of the Ribbon, you can click the Primary Key button Primary Key

The field that is made the primary key would then appear with a key icon to its left. When you click a field that is a primary key, the Primary Key button becomes highlighted Primary Key.

Practical Learning: Introducing Relationships

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. Type Ceil Inn1 (this is the name of a hotel) as the File Name of the database
  4. Click Create
  5. On the default table, double-click ID to put it into edit mode
  6. Type MaritalStatusID and press Enter
  7. In the menu that appears, click Short Text
  8. Type MaritalStatus
  9. Click the cell under MaritalStatus and type Single
  10. Press the down arrow key and type Married
  11. Close the table
  12. When asked whether you want to save, click Yes
  13. Set the name to MaritalsStatus
  14. Click OK
  15. On the Ribbon, click File and click Options
  16. In the left frame, click Current Database. In the right frame, click the Overlapping Windows radio button
  17. Click OK
  18. Read the message box and click OK
  19. On the Ribbon, click Create
  20. In the Tables section, click Table
  21. On the table, double-click ID to put it into edit mode
  22. Type FilingStatusID and press Tab
  23. In the menu that appears, click Short Text
  24. Type FilingStatus
  25. Click the cell under FilingStatus and type Unknown
  26. Press the down arrow key and type Head of Household
  27. Press the down arrow key and type Married Filing Jointly
  28. Close the table
  29. When asked whether you want to save, click Yes
  30. Set the name to FilingsStatus
  31. Click OK
  32. On the Ribbon, click Create
  33. In the Tables section, click the Table Design
  34. As the caret in blinking under Field Name, type EmployeeNumber and press Tab
  35. Click the arrow of the data type and select Number
  36. In the Tools section of the Ribbon, click the Primary Key button Primary Key
  37. Click the cell under EmployeeNumber and type FirstName and press the down arrow key
  38. Under FirstName, type LastName and press the down arrow key
  39. Under LastName, type Title
  40. In the left section of the title bar of Microsoft Access, click the Save button Save
  41. Set the name to Employees and press Enter
  42. On the Ribbon, click File and click Open
  43. In the list of files, click FunDS1 from the previous lesson
  44. On the Ribbon, click Create
  45. In the Tables section, click Table Design
  46. As the caret in blinking under Field Name, type EmployeeNumber
  47. Press Tab and type n.
    Notice that number has been selected
  48. Click the cell under EmployeeNumber and type FirstName
  49. Click the cell under FirstName and type LastName
  50. Click the cell under LastName and type Title
  51. Right-click EmployeeNumber and click Primary Key
  52. To save the table, right-click its tab and click Save
  53. Set the name to Employees and click OK
  54. On the Ribbon, click Create
  55. In the Tables section, click Table Design
  56. As the caret in blinking under Field Name, type ItemNumber
  57. Click the arrow the Data Type combo box and select Number
  58. By typing a field name and pressing the down arrow key, create the following fields:
    Manufacturer
    Category
    SubCategory
    ItemName
    ItemSize
  59. Right-click ItemNumber and click Primary Key
  60. To save and close the table, right-click its tab and click Close
  61. When asked whether you want to save, click Yes
  62. Set the name to StoreItems and press Enter

A Primary Key From Many Columns

A primary key can be made from a combination of many fields. To create a primary key of more than one field, display the table in Design View. Select, as a group, the fields that would constitute the primary key. Then:

  • You can right-click one of the selected fields of the group and click Primary Key

    Primary Key From Two Fields

  • In the Tools section of the Design tab of the Ribbon, you can click the Primary Key button Primary Key

The fields would then appear each with a key icon to its left:

Primary Key

A Foreign Key

A primary key makes it possible for a table to make its data available to other tables. If a table A wants to use the data stored in another table B, the first table, A, has to be prepared for it. The first table, A, can be called the child table. The other table can be called the parent table. The child table must have a field that would communicate with the parent table. That field represents the parent table. That field from the child table is called a foreign key. In the same way, any table that wants to use data from a certain table must have a foreign key that can communicate with the primary key of that parent table.

To make it easy to identify a foreign key in a table, it is a good idea, not a requirement, to give to the foreign key the same name as that of the primary key. The only real requirement is that both fields use the same data type.

Practical Learning: Introducing Relationships

  1. The Ceil Inn1 database should still be opened.
    In the Navigation Pane, right-click the Employees table and click Design View
  2. Complete the table with the following MaritalStatusID and FilingStatusID foregn keys:

    EmployeeNumber FirstName
    EmployeeNumber (Primary Key) Number
    FirstName  
    LastName  
    Title  
    MaritalStatusID Number
    HourlySalary  
    FilingStatusID Number
  3. Close the table
  4. When asked whether you want to save, click Yes
  5. In the Navigation Pane, double-click Employees to open its table
  6. Create the following records:
     
    EmployeeNumber FirstName LastName Title MaritalStatusId FilingStatusID
    22958 Andrew Laskin General Manager 1 2
    24095 Fred Barclay Accounts Associate 2 2
    27049 Harriett Dovecot Accounts Associate 2 3
  7. Close the Employees table

The Diagram of a Relationship

Introduction

The relationships among tables can be visualized 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:

    Database Tools

    In the Relationships section, click the Relationships button Relationships
  • If a table is currently displaying, on the Ribbon, click either Database Tools or Table

    Database Tools

    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:

Ribbon - Relationships Design

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 Show Table on the Ribbon
  • You can right-click the body of the Relationships window and click Show Table...

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 Ceil Inn1 database should still be opened.
    On the Ribbon, click Database Tools
  2. In the Relationships section, click the Relationships button Relationships.
    The Show Table dialog box should display (otherwise, click the Show Table button on the Ribbon):

    Show Table

  3. Click MaritalsStatus
  4. Click the Add button 
  5. In the Show Table dialog box, double-click Employees and FilingsStatus

    Relationships

  6. In the Show Table dialog box, click Close
  7. From the MaritalsStatus table, drag the MaritalStatusID field and drop it on top of the MaritalStatusID field in the Employees table:

    Relationships

     
    The Edit Relationship dialog box would come up

  8. Click Create
  9. Now you have a line relating these two tables.
    Drag any field from the FilingsStatus table and drop it on top of any field in the Employees table as if you missed the target

    Relationships

  10. 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 FilingStatusID and press Tab
  11. Under Related Table/Query, click the field that is selected. Click the arrow of its combo box and select FilingStatusID

    Edit Relationships

  12. Click Create

    Relationships

  13. On the Relationships section if the Ribbon, click the Close button Close
  14. When asked whether you want to save, click Yes
  15. On the Ribbon, click Create
  16. In the Tables section, click Table Design
  17. Under Field Name, type ReceiptNumber
  18. Press Tab and type n to select Number
  19. On the Ribbon, click the Primary Key button Primary Key
  20. Save the table as Payments and close it
  21. On the Ribbon, click Create
  22. In the Tables section, click Table Design
  23. Type AccountNumber
  24. Press Tab and type n to select Number
  25. Right-click AccountNumber and click Primary Key
  26. Complete the table with the following fields:
     
    Field Name Data Type Field Size
    AccountNumber (Primary Key) Number  
    FirstName Short Text 25
    LastName Short Text 25
    PhoneNumber Short Text 30
    EmergencyName Short Text 50
    EmergencyPhone Short Text 30
  27. Save the table as Customers and close it
  28. On the Ribbon, click Create
  29. In the Tables section, click Table Design
  30. Under Field Name, type RoomNumber
  31. Press Tab
  32. On the Ribbon, click the Primary Key button Primary Key
  33. Press F6 and type 10 for the Field Size
  34. Complete the table with the following fields:
     
    Field Name Data Type Field Size
    RoomNumber (Primary Key) Short Text 10
    RoomType Short Text 25
    BedType Short Text 20
    DailyRate Short Text 10
    RoomStatus Short Text 25
  35. Save the table as Rooms and close it
  36. On the Ribbon, click Create
  37. In the Tables section, click Table Design
  38. Type OccupancyNumber
  39. Press Tab and type n to select Number
  40. Right-click OccupancyNumber and click Primary Key
  41. In the left section of the toolbar of Microsoft Access, click the Save button Save
  42. Set the name of the table as Occupancies
  43. Click OK
  44. In the Navigation Pane, double-click Customers
  45. Create the following records
     
    AccountNumber FirstName LastName
    100752 Caroline Lomey
    946090 Peter Carney
  46. Close the Customers table
  47. In the Navigation Pane, double-click Rooms
  48. Create the following records
     
    RoomNumber RoomType BedType DailyRate RoomStatus
    104 Bedroom Queen 75.85 Available
    105 Bedroom King 92.75 Occupied
    106 Bedroom Queen 75.85 Available
    107 Bedroom King 92.75 Available
  49. Close the Rooms table
  50. On the Ribbon, click File and click Open
  51. In the list of files, click FunDS1 from the previous lesson
  52. On the Ribbon, click Create
  53. In the Tables section, click Table Design
  54. Type ReceiptNumber under Field Name
  55. Click the arrow the Data Type combo box and select Number
  56. Right-click ReceiptNumber and click Primary Key
  57. Click the cell under ReceiptNumber and type EmployeeNumber
  58. To save and close the table, click its Close button Close
  59. When asked whether you wan to save, click Yes
  60. Set the name to ShoppingSessions and click OK
  61. On the Ribbon, click Create
  62. In the Tables section, click Table
  63. Double-click ID, type SoldItemID and press Enter
  64. To save and change the view of the table, right-click its tab and click Design View
  65. Set the name to SoldItems and press Enter
  66. Click the cell under SoldItemID, type ReceiptNumber and press Tab
  67. In the combo box, select Number
  68. Click the cell under ReceiptNumber, type ItemNumber, press Tab, and type n
  69. By typing a field name and pressing the down arrow key, add the following fields:
    Manufacturer
    Category
    SubCategory
    ItemName
    ItemSize
  70. To save and close the table, right-click its tab and click Close
  71. When asked whether you want to save, click Yes
 
 
 

Establishing a Relationship With a Lookup Field

Using the Table Design View

There are various ways you can create and manage a relationship between two tables. Once you have a primary key in one table, you can ask Microsoft Access to create and even configure a foreign key for you. You can simply indicate where the data will come from; that is, you must indicate the table that holds the primary key, select the field that holds the actual data to use. For this approach, you use a lookup field.

To create a bound lookup field, you can open the table in Design View, set the data type of the field to Lookup Wizard... This would open the Lookup Wizard:

In the first page of the wizard, accept the first radio button

Since you are creating a field that would get its data from another table or query, you must select the first radio button and click Next. Then follow the wizard.

Practical Learning: Introducing Bound Lookup Fields

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Ceil Inn1 started earlier in this lesson
  3. In the Navigation Pane, right-click the Occupancies table and click Design View
  4. Click the field under OccupancyNumber
  5. Type EmployeeNumber and press Tab
  6. Set its Data Type to Lookup Wizard...
  7. In the first page of the wizard, accept the first radio button and click Next
  8. In the list of tables of the second page of the wizard, click Table: Employees

  9. Click Next
  10. In the Available fields list of the third page of the wizard, double-click FirstName
  11. In the Available Fiels list, while LastName is selected, click the right-pointing arrow button Select One

    In the Available fields list of the third page of the wizard, double-click RoomNumber

  12. Click Next
  13. In the fourth page of the wizard, click Next
  14. Accept the defaults in the fifth page of the wizard and click Next
  15. Click Finish
  16. When asked to save the table, click Yes.
    In the bottom section of the table, notice that the Field Size is set to Long Integer
  17. Click the empty field under EmployeeNumber and type DateOccupied and press F6
  18. Type 20 for the Field Size
  19. Click the empty field under DateOccupied and type AccountNumber
  20. Press Tab and type loo (to select Lookup Wizard...) and press Tab
  21. In the first page of the wizard, accept the first radio button and click Next
  22. In the list of tables of the second page of the wizard, make sure Table: Customers is selected and click Next
  23. In the Available fields list of the third page of the wizard, double-click FirstName
  24. Double-click LastName
  25. Click Next
  26. In the fourth page of the wizard, click Next
  27. Accept the defaults in the fifth page of the wizard and click Next
  28. Click Finish
  29. When asked to save the table, click Yes
  30. Click the cell under AccountNumber
  31. Type RoomNumber
  32. Press F6 and type 10 for the Field Size
  33. Add two more fields to the table as follows:
     
    Field Name Data Type Field Size
    OccupancyNumber (Primary Key)    
    EmployeeNumber    
    DateOccupied Short Text 20
    AccountNumber    
    RoomNumber Short Text 10
    RateApplied Short Text 10
    PhoneCharge Short Text 10
  34. Close the Occupancies table
  35. When asked whether you want to save, click Yes

Using the Table Datasheet View

You can also create a lookup combo box using the Datasheet View of a table. To do this:

  • To insert a new lookup fields between two columns, click the header of a column or an empty cell of the column that will succeed the new one. On the Ribbon, click Fields. In the Data Add & Delete section, click More Fields and click Lookup & Relationship

Lookup

  • To add a new lookup column at the end of the table, click Click to Add and click Lookup & Relationship

Lookup and Relationship

Any of these actions would open the Lookup Wizard.

When a column is a lookup field, if you don't like the way it behaves, you can reconfigure it. To do this:

  • Click a column header or a cell under it. On the Ribbon, click Fields. In the Properties section, click Modify Lookups Modify Lookups
  • Right-click the header of the column and click Modify Lookups

Practical Learning: Configuring Lookup Fields

  1. In the Navigation Pane, double-click Payments to open its table
  2. On the Ribbon, click Fields
  3. In the Add & Delete section, click the More Fields
  4. Cllick Lookup & Relationship
  5. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
  6. In the second page of the wizard, click the Table: Employees

  7. Click Next
  8. In the Availaable Fields list box, double-click FirstName
  9. Double-click LastName

    In the Available fields list of the third page of the wizard, double-click RoomNumber

  10. Click Next
  11. Click Next
  12. Click Next
  13. Replace the suggested Field1 name with EmployeeNumber
  14. Click Finish
  15. On the table, click Click to Add
  16. On the menu that appears, click Short Text
  17. Set the name to PaymentDate and press Enter
  18. On the menu that appears, click Lookup & Relationship
  19. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
  20. In the second page of the wizard, make sure Table: Customers is selected and click Next
  21. In the third page of the wizard, click FirstName and click the Select One button Select One
  22. In the Available Fields, as LastName is selected, click the Select One button Select One
  23. Click Next
  24. Click Next
  25. Click Next
  26. Replace Field1 with AccountNumber
  27. Click Finish
  28. Close the Payments table
  29. On the Ribbon, click Database Tools
  30. Click the Relationships button
  31. On the Ribbon, click Show Table
  32. In the Show Table, make sure Customers is selected (otherwise, click it).
    Press and hold Ctrl
  33. Click Occupancies
  34. Click Payments
  35. Click Rooms
  36. Release Ctrl
  37. In the Show Table dialog box, click Add
  38. Close the Show Table dialog box
  39. Establish a relationship using the EmployeeNumber from the Employees table to the Occupancies table
  40. Establish a relationship using the RoomNumber from the Rooms table to the Occupancies table
  41. Establish a relationship using the AccountNumber from the Customers table to the Occupancies table
  42. Establish a relationship using the EmployeeNumber from the Employees table to the Payments table
  43. Establish a relationship using the AccountNumber from the Customers table to the Payments table

    Lookup and Relationship

  44. Close Microsoft Access
  45. When asked whether you want to save, press Enter
 
 
   
 

Previous Copyright © 1997-2016, FunctionX, Inc. Next