combo box storing # not text

  • Thread starter Thread starter nydia
  • Start date Start date
N

nydia

i was told that it is not good to use the look up wizard
in a table because it actually ends up storing the id
instead of text. it was more correct to put a combo box
in the form, so there will be a list to pick from. Well,
I created a form and i put a combo box so that the user
can choose what department each person goes in, but when i
go to the actual table, it is storing the id, not the
department and when i run reports is showing numbers not
departments

human resource shows up as 1 instead of human resource

the properties for the table Department is text, why is it
storing numbers instead of actual text?? any help is
greatly appreciated
 
nydia said:
i was told that it is not good to use the look up wizard
in a table because it actually ends up storing the id
instead of text.
Click to expand...

You were told wrong. Storing the ID instead of the text is exactly what is
supposed to happen. The problem with using a Lookup field is that is
obscures this fact from the user. When you look at tables you should see
what is stored in them (aside from formatting issues), not data from some
other table.
it was more correct to put a combo box
in the form, so there will be a list to pick from. Well,
I created a form and i put a combo box so that the user
can choose what department each person goes in, but when i
go to the actual table, it is storing the id, not the
department and when i run reports is showing numbers not
departments

human resource shows up as 1 instead of human resource
Click to expand...

Again, exactly what should be happening. Any place you want to see the
text you use a query joining the two tables so the text can be pulled from
the lookup table or some other lookup mechanism (like a ComboBox).
 
The 'wizard' is just a tool for creating the combobox. You
can also create them manually.

A combobox is a 'control' for displaying or entering data
from a list on a form. A combobox 'may or may not' actually
store it's data in a underlying table field depending on the
purpose of the combobox. Some store data, some are used for
record navigation, some are used to set criteria for another
action.

A common setup for a combobox is to display, in your case,
the Department, but to actually store the DepartmentID which
is GOOD database design. It does this by hiding the first
column of data (DepartmentID) and displaying the second
(Department).

Why is it good to store the ID and not the 'name'?

1) Numeric ID's are more efficient for the database to index
and find.

2) You can always display the Department name through the
use of a query that ties the Department table to your
others.

3) If your Department name ever changes ( Janitorial
Services to Maintenance Engineering??? ), the change will
now be globally propagated across all of your records as the
ID remains the same.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
nYDIA said:
I used the look-up field before and it did store the
actual text for me, not the id. So, for proper database
creation, would you use the look-up wizard in a field, or
just use it for a form???
Click to expand...

There are two different issues coming into play here. Some lookup tables
have only one field containing the actual value to be looked up. For
example; a lookup field for the 50 states (if I were setting it up) would
simply have the 50 state abbreviation entries and that one field would be
the Primary Key on that table. There would not be a separate ID number
associated with each state because I am confident that the entries are
static with no duplicates

If I create a Lookup field in a table or use a ComboBox on a form that gets
its RowSource from the States lookup table then I would be storing the
exact text that I see in the drop-down list. A lookup field at the table
level based on this sort of lookup table would not be so bad since it
doesn't obfuscate what is being stored. The list is only for limiting
entries to known valid choices and/or to speed data entry.

Another developer might not feel comfortable with that lookup table. They
might be of the mindset that they want an ID field in the State lookup
table for a couple of reasons. A state abbreviation might change in the
future (consider North Dakota), or they might just feel that it would be
more efficient (storage wise) to store a numeric ID field in related
records instead of the two character string for the abbreviation.

Once the decision is made for the lookup table to have an ID field, then
that is what should be stored in related tables, not the text. Otherwise
there really is no point in the lookup table even having an ID field in the
first place. Furthermore, if what I am storing in related tables is the ID
field then that is what I should see when I look at the table directly.
This is where table-level lookup fields cloud the water. They present you
with tables where the data you see is not the data that the table actually
contains. The display of related data from other tables is properly done
with queries, forms, and reports. It does not belong in tables.

The next argument from the under-informed then is... "But now when I look
at the table I just see a bunch of numbers. I can't remember what state
number 23 is." The proper response to this objection is that tables are
not intended as a user interface. They are for raw data storage. If you
need to see the records with the state abbreviations displayed then you
create a query joining the table to the lookup table and pull the
abbreviation in. The fact that the base table is now less "user friendly"
is a non-issue.

As to your question above, the ComboBox (or other lookup mechanism) belongs
anywhere you want to put it *except for the table*. For data entry though
it would certainly be a form.
 
Back
Top