Solved Lookup List or Table - when to use (1 Viewer)

[Peter Peterson]

Good morning,
I am designing a small client management system for myself (outgrown MS Outlook).
I am using Office 365 Pro on latest version of Windows 10.

I am wondering when it is better (if ever) to use a Lookup List as opposed to a Table?
An example is 'Gender' where there is a choice of {Male, Female, Unspecified}.

At what point should a Table be used in preference to a Lookup List?

I look forward to your guidance.
Regards
Peter

 

[pbaldy]

I use tables almost exclusively. Even the gender one may have bitten you in recent years.

The table is maintainable by users, so I would only use a list if I was sure there would never be additions. Even then, if I'm going to want the list in multiple places, I'd rather just query the table instead of recreating the list.

Hopefully you aren't considering lookup fields:

The Access Web - The Evils of Lookup Fields in Tables

 

[The_Doc_Man]

I'm with Paul on this one.

A static lookup list in a form's combo box where your choices are HIGHLY limited can be useful. If you have more than about 40-50 characters in the list, you probably need to use a separate table to drive the combo. Offering a gender list of "Male, Female, Other/Unknown" is probably OK - but if that field would ever appear in a report, you would still need to be able to translate it unless those exact words are what you stored, as opposed to codes for the values.

A non-static lookup list is almost NEVER the right choice for a combo box because of the overhead involved in having to "diddle" the control source. If it can change, it belongs as a table. Even if the number of choices don't change, if the values DO then it belongs in a table.

A lookup FIELD (in a table) is NEVER a good idea because of the effect that it has on queries, particularly JOIN queries. We have literally dozens of threads involving problems that tie back to the fact that a lookup field involves an IMPLIED table and it becomes a real beast to extract information from the explicit table because of the ambiguities implied in the lookup field's implied table.

 

[MajP]

If I have a lot of different types of lookup choices, I make one big table
tblLookups
--lookupID
--lookupValue
--lookupCategory
--lookupSort

Then for combos I use the same rowsource just change the category
select lookupValue from tblLookup where lookupCategory = "YourCategory"

If it needs a special sort order not alphabetical or the in the order enter I can also sort the category.

 

[Pat Hartman]

Look up lists have been with us since we started developing computer applications. I first solved the problem in the 80's using COBOL and IMS. I later reproduced the app using COBOL and DB2. The final incarnation was Access with DB2 and then with SQL Server. I've included a copy using embedded ACE tables since that is easiest as a sample.