Why is ComboBox ID field needed?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Folks

I guess I'm getting brain-dead from coding - so here is a time out to really understand the index fields role
I always use the index and one or more other fields in the RowSource when designing a Table/Query
I do so blindly without thinking why I do it -- so, why do I need the index field
- Does it ensure uniqueness for each selectable dropdown control entry
- Is it needed or just fluff if all of the data elements are contained in one table

It would be nice if a tutorial explained the rational of this absolutely common design idiom.

Cheers

Sparky
 
It would be nice if a tutorial explained the rational of this absolutely common design idiom.
Click to expand...

Every table needs a Primary Key - some field (or combination of
fields) that is unique, stable, and (preferably) short.

An Autonumber ID is unique; stable - it can't be edited; and 4 bytes
long - so it's very convenient for this purpose.

Often the text values in a lookup table fail one or more of these
desiderata. For instance, if you have a table of employee names, you
might have two employees with the same name, an employee might change
their name, and the names will generally be longer than 4 bytes! (J.Lo
is an exception <g>).

It's a judgement call and a source of some controversy. As one
extreme, I have a table of States (and territories and Canadian
provinces) which does not have an Autonumber; I use the two-letter
postal abbreviation as the primary key. It's not *absolutely* stable
(Newfoundland and Nunavut have relatively recent new codes), but it's
unique, *mostly* stable, and only two bytes. Similarly, if you have a
table of Titles (Mr., Ms., Mrs., Miss, Dr.) there's no good reason to
have a second field.
 
Sparky said:
Folks:

I guess I'm getting brain-dead from coding - so here is a time out to
Click to expand...
really understand the index fields role.
I always use the index and one or more other fields in the RowSource when designing a Table/Query.
I do so blindly without thinking why I do it -- so, why do I need the index field ?
- Does it ensure uniqueness for each selectable dropdown control entry?
- Is it needed or just fluff if all of the data elements are contained in one table.

It would be nice if a tutorial explained the rational of this absolutely common design idiom.
Click to expand...
Every record in every table should have a unique ID. This is fundamental to
relational database theory and to a very large extent to practice. A
tutorial will not suffice.

If your combobox has a limited number of *never* changing values then you
can type them in and not use a key field.
If there is more than the ID allows you to keep just the ID in one table and
show other information in a query.
E.G. A customer ID would be entered and the query would display the name and
address. if the customer moves or if she gets married you only have to
change one table to reflect the new information.
 
Folks

Excellent postings - thanks so much. I thought the practice was needed to display a dropdown
And when I made a simple dropdown, it worked without the index
But, I finally realized that one must do something with a selection, such as use the ID in a join tabl
or other need where one doesn't want a string or other nonsensical data to clog up related fields

Again many thanks for your efforts - mission accomplished

Cheers

Spark
 
Back
Top