Home

Relationships and Data Integrity
 

Relational Databases
 

Introduction

When performing data entry, a user's job should be as easy and smooth as possible. One of the aspects that take care of this is the flow of information from one list to another. In other words, data stored in one list can be made available to another list so the user not only doesn't have to enter the same piece of information in different lists but also the should be able to access, from one list or table, data stored in another table.

To allow the information to flow from one list to another, there should (must) exist a relationship between both tables. A relationship is made possible through a type of link from one table to another. This is the essence of relational databases.

Practical Learning Practical Learning: Introducing Relational Databases
  • Start Microsoft Access and open the Yugo National Bank database you started in the previous lesson

The Key to Hold a Relationship

Once again, when performing data entry, the records that the user creates must be easily distinguishable. This means that each record must have some uniqueness with regards to the other records. To make this possible, you can create a field or column that sets apart each record. To do this, you can isolate a column that you know will hold unique values from one record to another. For example, if you were creating a database for the office that releases identity cards or driver's licenses, you certainly would like to make sure that two people don't have the same number.

 Relationship

The Primary Key of a Table
 

Introduction

The purpose of the primary key is to keep records distinct from one another. When performing data entry, you can let the user manage this and you can use some techniques to exercise a certain level of control. The data type of a primary key can be almost type, certainly a string or a number. For example, you can use an employee number or else. The most important rule is that each record must have a unique value to distinguish it from another record. In most cases, if you let the user specify the value(s) of the primary key, this can be overwhelming. Instead of going through this configuration process, you can rely on Microsoft Access to automatically create a unique value for each record of a particular column.

Creating a Primary Key in the Table Design View

A column that holds unique values that can differentiate one record from another is called a primary key. In most cases, you can create one column that would serve as the primary key of the table. To create a primary key, if working from the Design View of the table, you can right-click the column that will play this role and click Primary Key:

You can also click anywhere in the field and click the Primary Key button on the Table Design toolbar. After doing this, a picture with a key would appear on the left box of the column name. In some cases, you can use more than one column, that is, a combination of columns, to serve as the primary key. To do this, click the gray box on the left of one of the desired columns, press and hold Ctrl (or Shift). Click the gray box of the other column(s) to select. To make this combination the primary key, right-click the selection and click Primary key. Each of the selected columns would now display a pictured key on its gray box:

Based on a habit that follows a certain logic, the name of a column that holds the primary key usually ends with ID.

Because a primary key is very important and common in a relational database, in Microsoft Access, if you don't specify a primary key and start saving the table, a warning would inform you that your table doesn't have a primary key.

Practical Learning Practical Learning: Creating a Primary Key
  1. In the Tables section of the Database window, Click the New button
  2. In the New Table dialog box, double-click Design View
  3. Specify the first Field Name as WithdrawalTypeID
  4. Set its Data Type to AutoNumber
  5. To make it the Primary Key, while the field is still selected, on the Table Design toolbar, click the Primary Key button Primary Key and press F6
  6. Click Caption and type Withdrawal Type ID
  7. Under WithdrawalTypeID, type WithdrawalType
  8. In the lower section, click Caption and type Withdrawal Type
  9. Press the down arrow key and type Description
  10. Set the Description's Data Type to Memo
  11. Save the table as WithdrawalTypes and switch it to Datasheet View
  12. Close the table and, in the Tables section of the Database window, click WithdrawalTypes to make sure it is selected
  13. On the Database toolbar, click the arrow of the New Object button and click AutoForm
  14. Save the form with the default name
  15. Right-click the form and click Form Header/Footer
  16. Use the Command Button Wizard and add a Command Button to the right side of the Form Footer section
  17. Set the button's caption to Close and its name to cmdClose
  18. Adjust the design of the form as you see fit. Here is an example: 
     
  19. Enter a few records as follows:
     
    Withdrawal Type ID Withdrawal Type Description
    1 Cashier Money cashed at a bank
    2 ATM Automated Teller Machine
    3 Check Check issued by the customer or transaction authorized by the customer to another institution
    4 Drive-In Money cashed by the customer through the drive-in window
    5 Transfer Money transfer initiated by the customer
  20. Save and close the new form
  21. To create a new report, on the main menu, click Insert -> Report
  22. In the New Report dialog box, click AutoReport: Columnar
  23. In the combo box, select WithdrawalTypes and click OK
  24. To save the report, on the main menu, click File -> Save
  25. Accept the suggested name of the report and click OK
  26. Close the Report
  27. When asked whether you want to save it, click Yes
  28. To create a new report, on the main menu, click Insert -> Report
  29. In the New Report dialog box, click Report Wizard
 

The Data Type of a Primary Key

If you are creating a table in the Design View, to get the primary key column to automatically generate a unique incremental number, you can set its data type as AutoNumber.

If you are programmatically creating the table using SQL, in Lesson 14, we saw that you could apply the COUNTER or the AUTOINCREMENT data type to a column if you want it to generate an auto-incrementing numeric value.

Creating a Primary Key From the Table Wizard

The table Design View is the most common and probably the best place to create a primary key for a table. Still, you can use the table wizard to create a primary key. You make have noticed that the first column in the Sample Fields list of each Sample Table has a name that ends with ID:

When creating a table, if you select such a column and start creating the table, in the second page, the wizard would suggest that you let it create a primary key:

If you agree to let the wizard create the primary key, it would use the first ID column that was added to the table.
 

Practical Learning Practical Learning: Creating a Table Using the Table Wizard
  1. In the Tables section of the Database window, double-click Create a Table By Using Wizard
  2. In the Sample Tables list, click Employees
  3. In the Sample Fields, double-click the following fields: EmployeeID, EmployeeNumber, FirstName, LastName, Title, and Salary
  4. Click Next
  5. Accept the suggested name of the table as Employees and notice that a primary key will be created. Click Finish
  6. Right-click the title bar of the table and click Design View
  7. Click the empty field under Salary and type CanCreateNewAccount
  8. Set its Data Type to Yes/No and its Caption to Can Create New Account?
  9. In the upper section of the window, right-click Title and click Insert Rows
  10. Click the new empty field, type FullName and press F6
  11. Set the Field Size to 80 and its Caption to Full Name
  12. Save and close the table
  13. In the Tables section of the Database window, make sure the Employees table is selected
    On the Database toolbar, click the New Object: AutoForm button
  14. On the Form View toolbar, click the Save button
  15. Accept the suggested name of the form as Employees and click OK
  16. Right-click the title bar of the form and click Form  Design
  17. Double-click the FirstName text box to display its Properties window
  18. In the All tab of the Properties window, double-click On Lost Focus
  19. Click its Build button and implement the event as follows:
      
    Private Sub FirstName_LostFocus()
    On Error GoTo FirstName_Error
    
    Dim strFirstName As String
    Dim strLastName As String
    Dim strFullName As String
    
    strFirstName = [FirstName]
    strLastName = [LastName]
    
    If IsNull(strFirstName) Then
        strFullName = strLastName
    Else
        strFullName = strLastName & ", " & strFirstName
    End If
    
    [FullName] = strFullName
    Exit Sub
    
FirstName_Error:
    If Err.Number = 94 Then
        MsgBox "Make sure you provide a name for the employee"
    End If
End Sub
  20. Return to the form and click the LastName text box
  21. In the All tab of the Properties window, double-click On Lost Focus and click its Build button
  22. Implement the event as follows:
      
    Private Sub LastName_LostFocus()
    On Error GoTo FirstName_Error
    
    Dim strFirstName As String
    Dim strLastName As String
    Dim strFullName As String
    
    strFirstName = [FirstName]
    strLastName = [LastName]
    
    If IsNull(strFirstName) Then
        strFullName = strLastName
    Else
        strFullName = strLastName & ", " & strFirstName
    End If
    
    [FullName] = strFullName
    Exit Sub
    
FirstName_Error:
    If Err.Number = 94 Then
        MsgBox "Make sure you provide a name for the employee"
    End If
End Sub
  23. Return to the form and click FullName
  24. Set its Tab Stop to No
  25. Change the following properties for the EmployeeID text box:
    Enabled: No
    Locked: Yes
    Special Effect: Chiseled
  26. Complete the design the form as follows:
     
  27. Create a few records as follows:
     
    Employee ID Employee Number First Name Last Name Title Salary Can Create New Account?
    1 BM-0082-H2 Matt Yuen Head Cashier $22.82 Checked
    2 DX-6288-K4 Catherine Marconi Customer Account Manager $22.55 Checked
    3 FF-2799-G2 Leonie Ankoma Cashier $14.88  
    4 FH-1984-K2 Sylvie Young Regional Manager $16.22 Checked
    5 FO-2784-G0 Andy Holland Assistant Manager $24.12 Checked
    6 GG-6626-D3 Lienev Zbrnitz Cashier $15.75  
    7 GT-4825-L2 Paulin Santiago Intern $16.35  
    8 HD-3938-F4 Plant Waste Head Teller $16.75  
    9 KD-8230-H1 Steven Chang Accountant $16.15  
    10 KD-9377-H6 Abedi Kombo Shift Programmer $10.56  
    11 KS-1114-Y2 Samuel McCain Cashier $15.25  
    12 LS-9293-L3 Kirsten Roberts Cashier $18.05  
    13 MD-2286-F2 William Fake-Eye Public Relations $16.32  
    14 OR-0026-Z6 Roger Lamy Cashier $10.24  
    15 PC-2777-F8 Ada Zeran Administrative Assistant $15.48  
    16 PL-2783-G7 Milicien Drudge Cashier $18.34  
    17 TL-3825-G4 Aaron Kast Accounts Manager $12.34 Checked
    18 TR-7728-G5 Antoine Lourde Regional Assistant Manager $15.62 Checked
    19 UD-4050-X2 Lorraine Kirkland Assistant Manager $12.86 Checked
    20 WE-5552-F8 Jeffrey Salomons Cashier $24.52  
  28. Close the Employees form
  29. When asked whether you want to save it, click Yes
  30. In the Database window, click Tables and click Employees
  31. In the upper section of the window, click EmployeeNumber
  32. In the lower section of the window, click Caption and press Delete to delete the caption
  33. In the upper section of the window, click FullName
  34. In the lower section of the window, click Caption and press Delete to delete the caption
  35. Save and close the table
  36. To create a new report, on the main menu, click Insert -> Report
  37. In the New Report dialog box, click AutoReport: Columnar
  38. In the combo box, select Employees and click OK
  39. Switch the report to Design View and design it as you see fit
     
  40. Set the Force New Page property of the Detail section to After Section
  41. Preview the report
     
  42. Close the Report
  43. When asked whether you want to save it, click Yes
  44. Accept the default name and click OK
  45. On the main menu, click Insert -> Table
  46. In the New Table dialog box, double-click Table Wizard
  47. In the Sample Tables, click Customers
  48. In the Sample Fields double-click CustomerID
  49. In the Sample Tables, click Employees and, in the Sample Fields, double-click DateHired
  50. Click Rename Field, type DateCreated and press Enter
  51. Click the Personal radio button
  52. In the Sample Tables, click Accounts
  53. In the Sample Fields, double-click AccountNumber and AccountName
  54. Click Business radio button
  55. In the Sample Fields, double-click Address, City, State, PostalCode, Country, EmailAddress, and Notes
  56. Click Next and accept the suggested name of the table as Customers
  57. Click Finish
  58. Switch the table to Design View
  59. Click DateCreated in the top section.
    In the lower section, click Caption and type Date Created
  60. In the top section, edit Postal Code to display ZIPCode
  61. In the lower section, change its Caption to ZIP Code
  62. Right-click Notes and click Insert Rows
  63. In the new empty Field Name, type AccountStatus and set its Data Type to Lookup Wizard...
  64. In the first page of the wizard, click the second radio button and click Next
  65. Click under Col1 and type Active
  66. Press the down arrow key and type Suspended
  67. Press the down arrow key and type Closed
  68. Click Next and click Finish
  69. Set its Caption to Account Status
  70. Close the table and, when asked whether you want to save it, click Yes
  71. Create a few records as with sample customers records
  72. Close the Customers table
  73. To create a new report, on the main menu, click Insert -> Report
  74. In the New Report dialog box, click AutoReport: Columnar
  75. In the combo box, select Customers and click OK
  76. Save the report with the default name
  77. Switch the report to Design View and design it as you see fit. Here is an example:
     
  78. Set the Force New Page property of the Detail section to After Section
  79. Close the Report
  80. When asked whether you want to save it, click Yes
  81. Use the Table Wizard to create a new table based on the Accounts sample table of the Personal category and include only the AccountTypeID field. Rename AccountTypeID as TransactionTypeID
  82. Click Next
  83. Set the Name to TransactionTypes and click Finish
  84. Right-click the title bar of the table and click Table Design
  85. Under the existing field, add another field named TransactionType
  86. Under the existing field, add another field named Description and set its Data Type to Memo
  87. Change the Caption of the TransactionTypeID field to display Transaction Type ID
  88. Change the Caption of the TransactionType field to display Transaction Type
  89. Save the table and switch it to Datasheet View
  90. Perform data entry as follows:
     
    TransactionTypeID TransactionType Description
    1 Deposit Used if a customer is depositing money, regardless of the type of account
    2 Withdrawal Specifies that a customer is receiving money. This also applies when a check is cashed from the customer's account
    3 Fund Transfer This applies to an operation that consists of transferring money from one account to another
    4 Money Order This is selected if a person is purchasing a money order from this bank
    5 Service Charge There are various types of service charges. This category applies to all of them, regardless of the reason, as long as the Bank Management decides to withdraw money from the customer's account as a fee or a penalty
  91. Close the table
  92. In the Tables section of the Database window, make sure TrasactionTypes is selected and, on the Database toolbar, click the New Object: AutoForm button
  93. Close the form
  94. When asked whether you want to save it, click Yes and click OK
  95. To create a new report, on the main menu, click Insert -> Report
  96. In the New Report dialog box, click AutoReport: Columnar
  97. In the combo box, select TransactionTypes and click OK
  98. Close the Report
  99. When asked whether you want to save it, click Yes
  100. Accept the suggested name of the report and click OK
  101. Close the report
 

Programmatically Creating a Primary Key

Each of the libraries we have reviewed in the previous lessons provides a technique of specifying a column as the primary key or its table. If you are using either the Microsoft Access Object Library or DAO, to specify that a column is used as the primary, when calling the CreateField() method of the table as we saw in Lesson 10, pass a third argument as adKeyPrimary. Here is an example: 

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colStudentNumber As Object
    Dim colFullName As Object

    Set curDatabase = CurrentDb
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colStudentNumber = tblStudents.CreateField("StudentNumber", DB_LONG, adKeyPrimary)
    tblStudents.Fields.Append colStudentNumber
    Set colFullName = tblStudents.CreateField("FullName", DB_TEXT)
    tblStudents.Fields.Append colFullName
    
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

To create a primary using SQL, add the PRIMARY KEY (case-insensitive) expression on the right side of the column definition. Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE SeasonalEmployees(" & _
                 "ContractorNo LONG NOT NULL PRIMARY KEY, " & _
                 "AvailableOnWeekend LOGICAL NULL, " & _
                 "OwnsACar BIT, " & _
                 "CanShareOwnCar YESNO);"
End Sub

Remember that you can use the data type to influence how the numbers would be assigned.

 

Practical Learning Practical Learning: Programmatically Creating a Primary Key
  1. In the Database window, click Forms.
    Right-click AccountTypes and click Design View
  2. Double-click the button at the intersection of the rulers to open the Properties window and click Event
  3. Double-click On Load and click its ellipsis button
  4. To programmatically create a table using SQL, implement the event as follows:
      
    Private Sub Form_Load()
    DoCmd.RunSQL "CREATE TABLE Transactions(" & _
                 "TransactionID COUNTER(1001, 1) NOT NULL PRIMARY KEY, " & _
                 "TransactionDate DATE, " & _
                 "EmployeeID LONG, " & _
                 "CustomerID LONG, " & _
                 "TransactionTypeID LONG, " & _
                 "DepositAmount DOUBLE, " & _
                 "DepositTypeID LONG, " & _
                 "WithdrawalAmount DOUBLE, " & _
                 "WithdrawalTypeID LONG, " & _
                 "ServiceCharge DOUBLE, " & _
                 "ChargeReasonID LONG, " & _
                 "Notes MEMO);"
End Sub
  5. Return to Microsoft Access and switch the form to Form View to create the new table
  6. Close the form
  7. When asked whether you want to save the changes, click No
  8. In the Database window, click the Tables button and notice that a table named Transactions has been created
 
Previous Copyright © 2005-2016, FunctionX Next