Look Up tables vs Combo Boxes
- Thread starter KristenD
- Start date
"Lookup" tables and combo boxes generally go hand in hand. In other words, combo boxes usually get their data from a table, unless you use a Value List in the combo box Row Source. Maybe I'm not understanding your question.
Sorry, I meant Look Up tables versus using the Value List and Row Source in the tables.
I have 3 fields that have a "list" of options and I was trying to determine which would be the better way to go, using additional tables or using the Value list in the table.
I have 3 fields that have a "list" of options and I was trying to determine which would be the better way to go, using additional tables or using the Value list in the table.
- Local time
- Today, 10:05
- Joined
- Feb 19, 2002
- Messages
- 47,663
As Beetle said, they are not mutually exclusive. Lookups can use tables or embedded lists to provide the values you choose from. Your question should be whether it is better to use a table or an embedded list. And the answer is - it depends. For short lists that will never change, it is Ok to define the lists at the table level. Access will then give you the option of propagating those changes to all the forms and reports where the field is referenced - say yes
Always use tables for lists that you want the user to manage or that will change frequently. Long, static lists such as state or country names, rarely change but they are easier to manage as tables due to the number of entries.
Always use tables for lists that you want the user to manage or that will change frequently. Long, static lists such as state or country names, rarely change but they are easier to manage as tables due to the number of entries.Thank you! That is exactly the question I was trying to get to.
They are relatively short lists (3 max) and will likely never change unless the federal government decides to change up documents and hours and don't really think they have in the past 40 years since OSHA came into existence. And the other list is only 2 and labels the employee and seeing as they have labeled their employee's this way for almost 50 years I do not see this changing either.
I just wanted to make sure that I was doing it in the most "correct" way.
They are relatively short lists (3 max) and will likely never change unless the federal government decides to change up documents and hours and don't really think they have in the past 40 years since OSHA came into existence. And the other list is only 2 and labels the employee and seeing as they have labeled their employee's this way for almost 50 years I do not see this changing either.
I just wanted to make sure that I was doing it in the most "correct" way.
When pick lists grow large enough to crash a Combo Box control, then you will need to replace the Combo Box control with a pick list form.
If you have the pick list choices in a table rather than a Combo Box control, the migration will not cause the underlying application major upheaval. Queries which need to discern which pick list choice is currently selected for a given record still perform the same query... JOIN to the look-up table.
If you have the pick list only coded in the Combo Box, then in order to change a pick list value you not only need to update the Combo Box control, but also every query which needs to leverage that look-up field... as the pick list ends up coded into all of those queries as well.
Might be quick and simple looking at first (to hard code the pick list into the Combo Box control), but nasty in the long run.
If you have the pick list choices in a table rather than a Combo Box control, the migration will not cause the underlying application major upheaval. Queries which need to discern which pick list choice is currently selected for a given record still perform the same query... JOIN to the look-up table.
If you have the pick list only coded in the Combo Box, then in order to change a pick list value you not only need to update the Combo Box control, but also every query which needs to leverage that look-up field... as the pick list ends up coded into all of those queries as well.
Might be quick and simple looking at first (to hard code the pick list into the Combo Box control), but nasty in the long run.
