Introduction to Relationships

Lookup Fields

Unbound Lookup Fields

The fields we have been creating so far were classic text box fields that allow the user to simply type to create a value. Instead of letting the user type the values of a field, if you want, you can establish a list of values that are valid for a certain field. This would allow the user to simply select from that list. Such a field is called lookup field.

The simplest type of lookup field consists of creating a list whose items you know at the time you are creating the database. For example, if a clerk must specify the gender of persons whose records she is creating, since there are only two genders, you can create a list that

contains only male and female items. Since there would not be suspicion of a new value, you can safely create such a list. The lookup list is provided through a combo box. To set or change a value in the field, the user can expand the combo box and select an item. The item selected in stored in the field and is stored as a string (as text)

There are three main ways you can create a simple lookup field:

• In Datasheet View, create or select a field whose value should be selected from a list. Then, on the main menu, click Insert -> Lookup Column…
• In Datasheet View, create or select a field whose value should be selected from a list. Then right-click the column header and click Lookup Column
• In Design View, locate the field whose values should be selected from the list and set its Data Type to Lookup Wizard

Any of these actions would launch the Lookup Wizard. Since in this case you would be creating a known list, you should select the second radio button and proceed.

1. The Videos table of the Video Collection database should still be opened
   Right-click the Length column header and click Lookup Column...
2. On the First Page of the Lookup Wizard, click the second radio button
    
3. Click Next
4. Click the first empty field under Col1 and type G 
5. Press the down-arrow key and type PG 
6. Press the down-arrow key and type PG-13 
7. Type the other ratings you are aware of, if any:
    
8. Click Next
9. For the Column Label, type Rating and press Enter 
10. Set the ratings of the movies by selecting from the Rating combo box:
     
11. Close the Videos table
12. From the Resources folder, copy the Music Collection1 database to your Exercise folder and open it
13. Open the Artists table in Design View
14. Click the Structure field
15. For its Data Type, choose Lookup Wizard...
16. When the first page of the Lookup Wizard comes up, choose the second radio button telling the wizard that you will type values for the field, then click Next
17. Click the empty field under Col1 and type Solo
18. Press Tab and type Duo
19. Press Tab and type Band
20. Press the down arrow key and type Soundtrack
21. Press Tab and type Orchestra
22. Press the down arrow key and type Compilation
23. Press the down arrow key and type Other
     
24. Click Next. Accept the column label as Structure and click Finish
25. Click the Lookup tab in the lower part of the Design View
    To make sure that the user can only choose a preset value in the list, set the Limit To List field to Yes
    On the other hand, if you wanted to allow the user to type new entries, you would set the Limit To List field to No (keep in mind that even if you restrict the user to choose only from the list, you can eventually modify that list)
26. Save, and then close the table

Bound Lookup Fields

As done with the simple lookup, you can create a field whose data would be selected from a list. As opposed to an unbound lookup fields whose values you can predict at the time you are creating a database, a bound lookup field is one whose values are not known in advance. The values for such a field become available as the database is growing.

We mentioned earlier that tables could be linked to exchange information. Based on our illustration, a foreign key field from a table can be used to provide information to its table by

retrieving it from the originating table. When creating such a relationship, you should know what table would be providing such information and how the relationship would be managed.

To create a bound lookup field, you select a field and proceed as we saw for an unbound lookup field to open the Lookup Wizard. This time, you would select the first radio button and click Next. This would give you the opportunity to select the table that will supply the necessary information. In this case also, you can specify more than one column to be presented in the list.

1. Open the Video Collection database and open the Videos table in Datasheet View
2. Click any field under the Notes column.
3. On the main menu, click Insert -> Lookup Column...
4. In the first page of the Lookup Wizard dialog, make sure the first radio button is selected and click Next
5. On the second page, click VideoCategories:
    
6. Click Next
7. From the Available Fields list box, double-click VideoCategory
8. Click Next and Next
9. For the label, type Category and click Finish
10. Set the appropriate category for each movie
     
11. Close the table

MOUS Topics

Exercises 

1. Open the Watts A Loan database. Create a new table in Design View with the following fields:
    

   Field Name	Data Type	Additional Information
   TypeOfLoanID	AutoNumber	Primary Key Caption: Type of Loan ID
   TypeOfLoan		Caption: Type of Loan
   Description	Memo

   Save it as TypesOfLoan and close it

2. Using AutoForm, generate a form based on the TypesOfLoan table. Save it as TypesOfLoan and design it as follows before saving and closing it
    
3. Open the TypesOfLoan form and create the following types of loan:
    

   Type of Loan ID	Type of Loan	Description
   1	Personal	This loan is given as a cashier check to a customer who wants a cash loan
   2	Car	This loan will be processed by our partners as car dealers
   3	Boat
   4	Furniture
   5	Musical Instrument	We have some partnerships in musical instruments stores. This is the type of loan we will make available to the customers they find for us

4. Open the Transactions table. In the Datasheet View, create a new column using the Lookup (Column) Wizard. Include the AccountNumber and the LastName fields of the Customers table. Set its label to Account # then save and close the table
5. Open the Transactions table in Design View. Insert a new field just under TransactionID. Name it Processed By and start the Lookup Wizard. Include the LastName, the FirstName, and the Title fields from the Employees table. Save and close the table
6. Using AutoForm, generate a form based on the Transactions table. Save it as Transactions and design it as follows:
    
   
   Close the form

1. Open the Tenley Associates database and open the Employees table in Design View. Using the Lookup Wizard, configure the DepartmentName field so its value would originate from the DepartmentName Column of the Departments table. Set its label to Department. Save and close the table
2. Open the Employees form in Design View and delete the DeparmentName text box. Using the Field List, add the DepartmentID field to where the other was. Adjust the design of the form as follows:
    
   
   
   Save and close it

1. Open the Yugo National Bank and, in Design View, create a new table with the following fields:
    

   Field Name	Data Type	Additional Information
   ChargeReasonID	AutoNumber	Primary Key Caption: Charge Reason ID
   ChargeReason		Caption: Charge Reason
   Description	Memo

   Save it as ChargeReasons and close it

2. Using AutoForm, generate a form based on the ChargeReasons table and save it as ChargeReasons before closing it
    
3. Open the ChargeReasons form and create the following records
    

   Charge Reason ID	Charge Reason	Description
   1	Monthly Charge	Applied every month to all accounts
   2	Overdraft	Applied if a customer's account remains negative for 72 hours

   Close the form