If you've used any version of Microsoft Access you'll have found that you can spend lots of time entering data into tables, and sometimes you may type in the wrong data or misspell the correct data. You can make data entry easier and more accurate by ensure that data for a particular table field comes from a list you specify, and you want to be able to select this data from a pop down list. You can do this using the lookup feature. This article describes how to do this using a list you can type into the field property directly for data which never changes, or using a list from another table for data you may want to change from time to time.

Suppose you have a table containing a list of staff. This table includes the fields GENDER and JOB. You decide that you want to limit what data can be entered into the table. We'll work through the lookup for GENDER first. We want to be able to choose Male or Female only, and these choices will never change. For this reason we can enter the lookup values directly. So ensure the Staff table is open, then change to design view and select the GENDER field. In the lower part of the screen you'll see two tabs which contain all the field properties. Choose the Lookup tab.

Click into the first box in the lookup tab and change the option to Combo. This will give us the pop down showing the data available. Then lower down in the tab, against Row Source type, change to Value List. This will allow us to type in the actual data in the next box down. So in Row Source click into the white box on the right and type in "Male;Female" without the quote marks, but you must include the semicolon. Then lower down the same tab look for Limit to List and change the option to Yes. This will mean that the data entered can only be from the list. Then save the design changes and change back to datasheet view. Now choose any GENDER field, click the pop down and you'll see the two options. Try typing some other text, and Access will prompt you to say you can only use data from the value list.

Now we'll look at a different lookup requirement. Suppose you want to use a lookup for a different table field, but you want to be able to edit the list from time to time. For example the staff jobs are Manager, Accountant and Support, but they do change from time to time. To create a flexible lookup data source we need to create a new table, and then use the table as the lookup source. So we'll create another table, called, for example, TblJobs. This new table only has a single field JOB and this is also the primary key. Once the table is created and saved, you then enter the jobs Manger, Accountant and Support, and then close the table.

We want to use this table TblJobs as the data source for the JOBS field in the staff table. So open the staff table in design view, and select the Job field. Now we'll create the lookup. So, as before, in the lower properties sheet choose the Lookup tab, then choose Combo. Then lower down the tab we'll make a different choice compared to the Gender example. This time against Row Source type, leave the option as Table/Query. This is because the data we want to lookup is in another table. Then move the selection down one box to Row Source, click the pop down and choose the table TblJobs. Then lower down against set Limit to List Yes. Then save the design changes and change back to datasheet view.

To test the lookup, select and JOB field in the table and you should see your combo. Click the pop down and the Jobs currently in the TblJobs will be displayed. Try entering a job not in the table, such Driver, click off the cell and Access will display a warning prompt, and the data will not be added until you correct it by choosing from the combo. Then close the table.

Try opening TblJobs again, and add two more jobs, then close the table. Then open the staff table again and select any JOB field, click the combo pop down and you'll see the changed list containing the extra jobs.

So that completes our look at using lookups for data entry in Access table fields. There are two kinds, value lists and tables. Value Lists are entered directly into the property sheet and are ideal for lists with fixed values. Tables are best used for lookup data sources which you can easily change. Interested in learning much more about the many features of Microsoft Access? A really effective way is to attend a training course, and then your use of Access can really take off.