How to Look Up Values From One Microsoft Access Table and Automatically Insert Them in Another Table

You can put values from one table into another with a Lookup list. For example, if you were an artist, you might have one table for your paintings and another table for the galleries that display them. With a Lookup list of gallery names from the Gallery table inserted into a field in the Paintings table, you could simply click on the correct gallery from a menu instead of typing it from the keyboard. These instructions apply to MS Access 97.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• Microsoft Access

Create a Lookup List from Design View

1. Open your table in Design view.

2. Insert a new row in the spot where you want your new field to appear. If you want to put the new field at the end, click in the first empty row.

3. Name your field.

4. Click in the datatype box immediately to the right. A small arrow appears in the right corner.

5. Click in the small arrow to view a menu of choices. Select Lookup Wizard.

6. When the wizard appears, select "I want the Lookup Column to look up values in a table or query."

7. Follow the wizard's instructions. Click Finish when satisfied.

8. Save your table. When you look at your table in Datasheet view, clicking on the empty field will provide a menu of choices based on the field values that you "looked up."

Create a Lookup List from Datasheet View

9. Open your table in Datasheet view.

10. Create a new column where you want your new lookup field to appear.

11. Name your new field.

12. From the Insert menu, select Lookup Column.

13. When the wizard appears, follow the same procedures mentioned above.

14. Save your table.