why are look-up fields cautioned against?

On Wed, 19 Apr 2006 20:21:06 -0400, "Fred Boer"
wrote:

Dear John:

Sigh. Thanks for keeping me on the straight and narrow.

Could I explore this a bit more, though? What's the best way to cope with
this? I have a city table which I use to list the place of publication for
books. I also have a Province/State table listing, well, province and state
names (used for Patron address information, actually)... Until now, I've
just ignored the fact that different places might share the same name, but
your correction has made me think. Should I do something like this if I
wanted to be more accurate? (And I suppose that country name data would
possibly be involved, but let's limit it to provinces and U.S. States...)


tblCity

CityID
CityName
ProvinceOrStateID

tblProvinceOrState

ProvinceOrStateID
ProvinceOrStateName

There are actually two cities named Los Alamos in New Mexico (one's
off on a dirt road twenty miles from Las Vegas, oddly enough) so you
can't even count on a two-field city/state index. I don't know if
there IS a really good way, though the US Postal Service's ZIP code
database (with its Preferred and Alternate City Names) may be the best
bet...

John W. Vinson[MVP]

Clearly there is potential murkiness with storing CityID by itself. If one
Los Alamos changes its name there is no way of editing the record in tblCity
without every Los Alamos assuming the new name. The combo box from which
City is selected could be a two-column combo box that includes the Zip code,
but what happens is the municipality grows to the point where it has two or
more Zip codes, or if a larger city has two or more Zip codes to start with,
or if there is Zip code from, say, Las Vegas that has not yet been used?
Since City is a single field I would be inclined to store the name rather
than the ID, although a database that includes only local addresses may be
handled differently from one that includes a nationwide listing in which
some towns may appear only once. So many decisions...

"John Vinson" wrote in message
...
On Wed, 19 Apr 2006 20:21:06 -0400, "Fred Boer"
wrote:

Dear John:

Sigh. Thanks for keeping me on the straight and narrow.

Could I explore this a bit more, though? What's the best way to cope with
this? I have a city table which I use to list the place of publication for
books. I also have a Province/State table listing, well, province and
state
names (used for Patron address information, actually)... Until now, I've
just ignored the fact that different places might share the same name, but
your correction has made me think. Should I do something like this if I
wanted to be more accurate? (And I suppose that country name data would
possibly be involved, but let's limit it to provinces and U.S. States...)


tblCity

CityID
CityName
ProvinceOrStateID

tblProvinceOrState

ProvinceOrStateID
ProvinceOrStateName

There are actually two cities named Los Alamos in New Mexico (one's
off on a dirt road twenty miles from Las Vegas, oddly enough) so you
can't even count on a two-field city/state index. I don't know if
there IS a really good way, though the US Postal Service's ZIP code
database (with its Preferred and Alternate City Names) may be the best
bet...

John W. Vinson[MVP]

Thanks to you both!

Fred


"BruceM" wrote in message
...
Clearly there is potential murkiness with storing CityID by itself. If
one Los Alamos changes its name there is no way of editing the record in
tblCity without every Los Alamos assuming the new name. The combo box
from which City is selected could be a two-column combo box that includes
the Zip code, but what happens is the municipality grows to the point
where it has two or more Zip codes, or if a larger city has two or more
Zip codes to start with, or if there is Zip code from, say, Las Vegas that
has not yet been used? Since City is a single field I would be inclined to
store the name rather than the ID, although a database that includes only
local addresses may be handled differently from one that includes a
nationwide listing in which some towns may appear only once. So many
decisions...

"John Vinson" wrote in message
...
On Wed, 19 Apr 2006 20:21:06 -0400, "Fred Boer"
wrote:

Dear John:

Sigh. Thanks for keeping me on the straight and narrow.

Could I explore this a bit more, though? What's the best way to cope with
this? I have a city table which I use to list the place of publication
for
books. I also have a Province/State table listing, well, province and
state
names (used for Patron address information, actually)... Until now, I've
just ignored the fact that different places might share the same name,
but
your correction has made me think. Should I do something like this if I
wanted to be more accurate? (And I suppose that country name data would
possibly be involved, but let's limit it to provinces and U.S. States...)


tblCity

CityID
CityName
ProvinceOrStateID

tblProvinceOrState

ProvinceOrStateID
ProvinceOrStateName

There are actually two cities named Los Alamos in New Mexico (one's
off on a dirt road twenty miles from Las Vegas, oddly enough) so you
can't even count on a two-field city/state index. I don't know if
there IS a really good way, though the US Postal Service's ZIP code
database (with its Preferred and Alternate City Names) may be the best
bet...

John W. Vinson[MVP]

On Thu, 20 Apr 2006 07:41:51 -0400, "BruceM"
wrote:

Clearly there is potential murkiness with storing CityID by itself. If one
Los Alamos changes its name there is no way of editing the record in tblCity
without every Los Alamos assuming the new name. The combo box from which
City is selected could be a two-column combo box that includes the Zip code,
but what happens is the municipality grows to the point where it has two or
more Zip codes, or if a larger city has two or more Zip codes to start with,
or if there is Zip code from, say, Las Vegas that has not yet been used?
Since City is a single field I would be inclined to store the name rather
than the ID, although a database that includes only local addresses may be
handled differently from one that includes a nationwide listing in which
some towns may appear only once. So many decisions...

What I've usually done is use a Zip table with fields for Zip, City,
and (redundantly though it helps for filtering) State. I've got a
generic Address subform which filters the City combo box on updating
the Zip, and vice versa; if selecting a zip uniquely identifies a city
(typical) it just fills it in, and if selecting a city uniquely
identifies a zip (small towns), it just fills THAT in.

John W. Vinson[MVP]

Dear Fred

It's been some time since your posting that I'm replying to here, but I just
needed to tell you that your advice really gave me a big push upwards on my
Access learning curve. I'm glad I asked the question and I'm continuing to
absorb and digest what I what you've taught me here. Thanks very much!


"Fred Boer" wrote:

Dear David:

At the risk of making it look like you are being mobbed...

No. Lookup fields are not necessary. Looking up data in related tables *is*,
but *not* using lookup fields. When I started with Access it took several
tries before one of the experts here actually got through to me on this...

Maybe an example might help? (Apologies if this seems simplistic...)

Suppose you have a database listing customers. They live all over the world.
Every customer address includes a city. .

tblCustomer

CustomerID
Name
City

Aha! You realize that you should have a separate table for city names! So
you create a table for cities, maybe something like this:

tblCities

CityID
CityName

So, you want to change the customer database, right?

tblCustomer

CustomerID
Name
Ci...

Hold it... you think.. shouldn't that be a lookup field? I mean, I want to
"look up" that information in the "cities" table, right?
At this point, you need to stop. Don't create a lookup field in the
"customer" table. Create a field called "CityID". Make it the "number"
datatype.
Now, close and save the new table design. Go to ToolsRelationships (or
click on the button). Add both tables to the design form. Click on the
"CityID" field in tblCustomer and drag it on top of the "CityID" field in
tblCities. A relationship window opens up. Check the enforce referential
integrity checkbox, the click on "Create". You have created the necessary
relationship.

Now... what next? Well, you *don't* use the datasheet to do data entry. The
next step is to create a form. (Use the wizard...). Then open this form in
design view. Click on the combobox button in the toolbox. Drag a combobox on
the form. The combobox wizard will run. Choose the option to look up the
values in another table. Follow the wizard.

Congrats! You have a combobox which presents a list of cities to choose
from. The combobox is bound to the CityID field in tblCustomer.

So you can "look up" the data in a related table, but you don't have a
"lookup field" in the Customer table...

HTH
Fred

P.S. BTW, thanks Klaatu for picking up my fumble earlier in the thread.
P.P.S. Of couse tblCities doesn't necessarily need a CityID field (Primary
key could be CityName)...
P.P.P.S. And, of course, one could also use a form/subform..
P.P.P.P.S. We'll be here when you try to figure out exactly how the combobox
works!



"dbnDavid" wrote in message
...
Thank very much Fred and Klatuu. I'm continuing to digest the info, but it
has certainly clarified things for me. The short message I get is that
lookup
fields do have to be present in a table as a necessary part of table
relationships, but that's all you ask them to do!

Happy to been of help, David! Good luck in your project!

All the best!
Fred

"dbnDavid" wrote in message
...
Dear Fred

It's been some time since your posting that I'm replying to here, but I
just
needed to tell you that your advice really gave me a big push upwards on
my
Access learning curve. I'm glad I asked the question and I'm continuing to
absorb and digest what I what you've taught me here. Thanks very much!


"Fred Boer" wrote:

Dear David:

At the risk of making it look like you are being mobbed...

No. Lookup fields are not necessary. Looking up data in related tables
*is*,
but *not* using lookup fields. When I started with Access it took
several
tries before one of the experts here actually got through to me on
this...

Maybe an example might help? (Apologies if this seems simplistic...)

Suppose you have a database listing customers. They live all over the
world.
Every customer address includes a city. .

tblCustomer

CustomerID
Name
City

Aha! You realize that you should have a separate table for city names!
So
you create a table for cities, maybe something like this:

tblCities

CityID
CityName

So, you want to change the customer database, right?

tblCustomer

CustomerID
Name
Ci...

Hold it... you think.. shouldn't that be a lookup field? I mean, I want
to
"look up" that information in the "cities" table, right?
At this point, you need to stop. Don't create a lookup field in the
"customer" table. Create a field called "CityID". Make it the "number"
datatype.
Now, close and save the new table design. Go to ToolsRelationships (or
click on the button). Add both tables to the design form. Click on the
"CityID" field in tblCustomer and drag it on top of the "CityID" field
in
tblCities. A relationship window opens up. Check the enforce referential
integrity checkbox, the click on "Create". You have created the
necessary
relationship.

Now... what next? Well, you *don't* use the datasheet to do data entry.
The
next step is to create a form. (Use the wizard...). Then open this form
in
design view. Click on the combobox button in the toolbox. Drag a
combobox on
the form. The combobox wizard will run. Choose the option to look up the
values in another table. Follow the wizard.

Congrats! You have a combobox which presents a list of cities to choose
from. The combobox is bound to the CityID field in tblCustomer.

So you can "look up" the data in a related table, but you don't have a
"lookup field" in the Customer table...

HTH
Fred

P.S. BTW, thanks Klaatu for picking up my fumble earlier in the thread.
P.P.S. Of couse tblCities doesn't necessarily need a CityID field
(Primary
key could be CityName)...
P.P.P.S. And, of course, one could also use a form/subform..
P.P.P.P.S. We'll be here when you try to figure out exactly how the
combobox
works!



"dbnDavid" wrote in message
...
Thank very much Fred and Klatuu. I'm continuing to digest the info,
but it
has certainly clarified things for me. The short message I get is that
lookup
fields do have to be present in a table as a necessary part of table
relationships, but that's all you ask them to do!