Setting Criteria for Fields with Combo Box Lists in Queries

[Guest]

I set up a database that has 8 tables for data entry. Of those 8 tables, 4 of
them have columns with drop down combo box lists (7 smaller tables that list
the values in the combo boxes) and 2 of them have yes/no check boxes. The
main table (Store Information) has Store Number as the Primary Number. The
other 7 tables have either a one-to-one or one-to-many relationship with the
Store Information table.

My question is this:

I put together a query that brings in data from two or more tables. I want
to generate a report by a specific TYPE (column name) based off of this
query. The column that stores the value for the TYPE is a drop down combo box
(which lists remodel, new, relocation...etc). I only want the results to show
'remodel' of course but either all records show up or no records show up. How
do I fix this problem?

Thanks,
T

 

[Jeff Boyce]

I may not have understood ... it sounds like you have used "lookup" data
types in the tables. If so, be aware that what is displayed and what is
stored are not (necessarily) the same. A scan through the tablesdbdesign
newsgroup will reveal a strong consensus against using lookup data type
fields, because of the risk of confusion.

Instead, eliminate the combo box/lookup data type and simply store the
foreign key to the other table(s). Then, in your queries, join the main
table and the lookup tables (lookup tables are fine, lookup fields are
risky). In your query, select the human-readable field from the lookup
table(s), instead of the rowID.

Regards

Jeff Boyce
Microsoft Office/Access MVP