Solved Look Up vs List Box

[sctb0825]

I have a field that I use on several forms, ie Mr. Mrs, Ms, Prof, Dr. Hon.

Question? is it better to put these in a table and use Look Up on each form or to use a List box on each form?

 

[sctb0825]

In addition I have several similar List that I use the same way.

 

[theDBguy]

Hi. Definitely use a table for lookup values. As far as which control to use on your form, you can use either a combobox or a listbox, depending on your taste or design preferences.

 

[theDBguy]

In addition I have several similar List that I use the same way.

Depending on the number and depth of them, you can either use separate tables for each, or combine all of them into one table. However, a single table lookup will require some additional design considerations. For example, if some of your lookup values are numeric while others are text, then you will probably have to convert the numeric values into text, to be able to keep all the lookup values in one column in the same table. You would then, probably, have to convert them back into numbers in your forms where you would use the lookups.

 

[sctb0825]

So to be clear, I would design it in the table as a lookup the use it on my form as a combo or listbox?

 

[sctb0825]

Do I need to create a query to go with that setup

 

[theDBguy]

So to be clear, I would design it in the table as a lookup the use it on my form as a combo or listbox?

Just to be clearer, you don't want to design the lookup at the table level. Instead, you would use a combo or listbox on the form.

The Access Web - The Evils of Lookup Fields in Tables

 

[sctb0825]

Just to be clearer, you don't want to design the lookup at the table level. Instead, you would use a combo or listbox on the form.

The Access Web - The Evils of Lookup Fields in Tables

Let me be more specific, I have a table with just the list, then let's say in another table called Customers, I would include a field that has list table and define the field in the Customers table as a Lookup? or Short Text?
Or, just use Combobox or listbox in form?

 

[theDBguy]

Let me be more specific, I have a table with just the list, then let's say in another table called Customers, I would include a field that has list table and define the field in the Customers table as a Lookup? or Short Text?
Or, just use Combobox or listbox in form?

Okay, let's say you have a table with a list of values you want to look up. This table should have a primary key field. For example:

ID   Payment
1    Cash
2    Credit Card
3    Check

Then, in another table, you want to select those values as a look up. The structure of that table might look something like this:

ID   Payment   Date
1    1    1/14/2022
2    3    1/12/2022

That's what the table will look like. However, on the data entry or edit form, that payment type will show the text values (Cash, Credit Card, etc.)

Hope that makes sense...

 

[GPGeorge]

At root, the question comes down to the integrity of the relational database design.

Dropdowns , or combo boxes, are INTERFACE features. I.e. they are a way to allow users to interact with data via an object designed for that interaction. Interface features belong in the interface, i.e. in forms. They are NOT data constructs. They do NOT belong in tables.

 

[CJ_London]

and just to add - just because you can doesn't mean you should

 

[GPGeorge]

Someone needs to send that memo around MS because they don't seem to get it.

Not putting words into anyone's mouth or trying to infer motives, but my impression is that leaving lookup fields in tables probably reflects how hard it is to walk back mistakes loudly and publicly asserted. Pulling them out would be tantamount to admitting a mistake; it won't happen.