Home

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.

Practical Learning: Setting Preset List Of Items

  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.

Practical Learning: Getting Fields Data From External Source

  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

S3 Establish table relationships
S10 Set primary keys
S13 Modify tables using Design View
S14 Use the Lookup Wizard
S33 Establish relationships
 

Exercises 

 

Watts A Loan

  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

Tenley Associates

  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

Yugo National Bank

  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

 

Previous Copyright © 2002-2007 FunctionX, Inc. Next