How To Add Lookup Field Based On Another Table Or Query In Access 2010

Database building may be the most critical step in designing a new Information system. If you use Microsoft Access 2010, you may search for the tools that enable you reduce the rate of mistakes and misspells in data entry. Among of these, Access 2010 has a feature to base a new column in your table on a column in another table or query you created before in your database, so you can fill the records in the new column by choosing a value from a drop down list. This process can be explained in the following steps:

1- In design view, open the table that you want to add the new column into. Write the field name in the first empty row and choose Lookup Wizard from the data type drop-down list. Access lunches the lookup wizard.

2- With the “I want the lookup field to get the values from another table or query” option selected, click next. Access displays the tables and queries in your database. You can now click the source table or query. You can also choose to display tables or queries or both of them in the list.

3- After you select the source table or query, click next. The wizard displays a list of the fields of your table or query to let you choose the source column.

You can use the arrows to add or remove column(s).

4- Select your desired column then click next, Access then lets you sort the list of values.

5- In the last two steps of the wizard, Access displays the list and let you adjust its width.

Then Access asks you to name the new column, where you can also apply data integrity to organize the relation between the two columns.

When you click finish, you can display the new column’s table in the datasheet view, and try to add data to it, you will see a drop down list contains values from your source column.