Using MS Access/Query / Look up Fields


I am an intermediate user with Access 2010. I'm finding that when I create a table with a lookup field, if I point the look up field to another table for information, I'm not able to filter that data in a query. I have found that if I "type the list" in the table lookup field (instead of pointing to another table), I am able to use the "criteria" filter in a query.

The problem with this is I have multiple tables with this same look up information. Currently I have to manage all the table lookup fields rather than one table that I can just point all the lookup fields to.

I hate, hate, HATE table-based lookup fields. You should set them up the old-fashioned way using a foreign key field linked to your 2nd table. Use a combo box on a form to display the list of options, but DON'T store the data in your table in a lookup field. See this tutorial: RELATIONAL COMBO BOX.

Be sure to visit my web site and watch my FREE 2-hour-long Microsoft Access Video Tutorial

Richard Rost
599CD Computer Training

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Richard Rost


I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.


I am the president of and I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at

©2017 All rights reserved.