Using MS Access/Query / Look up Fields

Advertisement


Question
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.

Answer
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

Cordially,
Richard Rost
599CD Computer Training

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

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.

Experience

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

Education/Credentials
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 599CD.com

©2016 About.com. All rights reserved.