LOOKUP Causing Several Problems

[Jennifer K.]

I have some strange problems happening with some lookup lists created in a
table using the Lookup Wizard.

I have a field for State of Residence [GRES] that is a number field
(integer, 2 characters). I have a separate table that has three fields that
include state name, state abreviation, and the number code that we store to
represent a state. For example:

STATE ST_ABBREV CODE_STATE
Alabama AL 1
Arizona AZ 2
Arkansas AR 3
California CA 4
..
..
Canada - Alberta A­LTA 55
Canada - Saskatchewan S­ASK 55

The person entering data should be able to choose California and have the
number 4 stored as the value in the field. Anything in Canada would be stored
as 55.

What is actually happening seems to be that the text "California" is being
stored and not the number. Somehow it seems to be changing the Data Type from
number to text.

Also, when I try and delete that field from the table it tells me that I
have relationships that need to be disposed of before I can delete. Even
after I have eliminated those relationships it continues to give me that
message and not allow me to delete the field. If I manage to leave the table
and then re-open it than I can delete the field.

For the states this has been an aggrevation, but there is a larger lookup
that needs to be used that looks at place name and uses a code that specifies
the actual town and county using a code. For example:

TOWN_NA PLACE_NA CTC_CODE
SHOREHAM LAPHAM BAY 118

An event that takes place in Lapham bay should be coded as 118 to represent
Shorham in Chittenden County. Instead of storing 118 it is storing the text
Shorham.

Insight into what is going on and a solution would be VERY much appreciated.

Thanks,
Jennifer

 

[Graham Mandeno]

Hi Jennifer

Almost certainly, what you are seeing is a combo box which displays the
state name and is hiding the code value that is really being stored.

You might find this article interesting:
http://www.mvps.org/access/lookupfields.htm

There is a problem with giving all the Canadian states the same code. If
the person entering the data selects SASK as the state and the code number
55 is stored, then when the data is retrieved there is no way of knowing
whether the state is SASK or any of the other Canadian states.

Note also that you should be using forms, not tables, for data entry.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have some strange problems happening with some lookup lists created in a
table using the Lookup Wizard.

I have a field for State of Residence [GRES] that is a number field
(integer, 2 characters). I have a separate table that has three fields
that
include state name, state abreviation, and the number code that we store
to
represent a state. For example:

STATE ST_ABBREV CODE_STATE
Alabama AL 1
Arizona AZ 2
Arkansas AR 3
California CA
4
.
.
Canada - Alberta A­LTA 55
Canada - Saskatchewan S­ASK 55

The person entering data should be able to choose California and have the
number 4 stored as the value in the field. Anything in Canada would be
stored
as 55.

What is actually happening seems to be that the text "California" is being
stored and not the number. Somehow it seems to be changing the Data Type
from
number to text.

Also, when I try and delete that field from the table it tells me that I
have relationships that need to be disposed of before I can delete. Even
after I have eliminated those relationships it continues to give me that
message and not allow me to delete the field. If I manage to leave the
table
and then re-open it than I can delete the field.

For the states this has been an aggrevation, but there is a larger lookup
that needs to be used that looks at place name and uses a code that
specifies
the actual town and county using a code. For example:

TOWN_NA PLACE_NA CTC_CODE
SHOREHAM LAPHAM BAY 118

An event that takes place in Lapham bay should be coded as 118 to
represent
Shorham in Chittenden County. Instead of storing 118 it is storing the
text
Shorham.

Insight into what is going on and a solution would be VERY much
appreciated.

Thanks,
Jennifer

 

[Ken Sheridan]

Jennifer:

So-called lookup fields are bad news, as you'll see if you go to the link
Graham gave you, and as your own experience has confirmed.

You might like to take a look at the file which can be downloaded from then
following link:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

Its principally intended to show how correlated combo boxes can be used when
dealing with hierarchical data, but as it uses the geographical units of
Parish, District and County in my area its pertinent to your scenario.
You'll see that the Locations table shows the numeric ParishID, but on the
forms you see the parish name, i.e. what you are aiming at. In fact your
table probably does currently store the numeric values, but because the
lookup field wizard has been used you don't see the true value.

The key thing about the demo file is that it allows top-down selection,
(County then District then Parish) while preserving normalization (to Third
Normal Form at least). The Locations table has a ParishID column, but no
DistrictID or CountyID columns. The ParishID implies the DistrictID which in
turn implies the CountyID, so having DistrictID or CountyID columns in the
Locations table would introduce redundancy, and leave the door open to
inconsistent data.

The ability to use top down selection might not interest you, but you should
ensure that your tables are similarly normalized, and that the referencing
table includes a foreign key column only for the lowest geographical unit in
the hierarchy. This appears to be 'Places' from what you've said in your
post, so your referencing table would include a place_code column, which
would reference the key of a Places table. The Places table would include a
ctc_code code column referencing the key of Towns.

Finally take note of what Graham says about the Canadian provinces. The
code_state column, being the column referenced by another table, is the
States table's primary key (or at least a candidate key) so must have unique
values. But why use a 'surrogate' numeric key at all? The st_abbrev column
is also a candidate key so can be the table's primary key and a foreign key
in any table which references the States table. Surrogate numeric keys are
only necessary where a 'natural' key is unavailable (strictly speaking a
'natural' key is always available as multi-column keys can be used, but this
can get cumbersome and a surrogate numeric key is often more convenient).
Instances where surrogate keys are appropriate are with entity type like
towns or places as town or place names are often duplicated.

Ken Sheridan
Stafford, England

I have some strange problems happening with some lookup lists created in a
table using the Lookup Wizard.

I have a field for State of Residence [GRES] that is a number field
(integer, 2 characters). I have a separate table that has three fields that
include state name, state abreviation, and the number code that we store to
represent a state. For example:

STATE ST_ABBREV CODE_STATE
Alabama AL 1
Arizona AZ 2
Arkansas AR 3
California CA 4
.
.
Canada - Alberta A­LTA 55
Canada - Saskatchewan S­ASK 55

The person entering data should be able to choose California and have the
number 4 stored as the value in the field. Anything in Canada would be stored
as 55.

What is actually happening seems to be that the text "California" is being
stored and not the number. Somehow it seems to be changing the Data Type from
number to text.

Also, when I try and delete that field from the table it tells me that I
have relationships that need to be disposed of before I can delete. Even
after I have eliminated those relationships it continues to give me that
message and not allow me to delete the field. If I manage to leave the table
and then re-open it than I can delete the field.

For the states this has been an aggrevation, but there is a larger lookup
that needs to be used that looks at place name and uses a code that specifies
the actual town and county using a code. For example:

TOWN_NA PLACE_NA CTC_CODE
SHOREHAM LAPHAM BAY 118

An event that takes place in Lapham bay should be coded as 118 to represent
Shorham in Chittenden County. Instead of storing 118 it is storing the text
Shorham.

Insight into what is going on and a solution would be VERY much appreciated.

Thanks,
Jennifer