replace numeric values with county names

[Takeadoe]

Brand New to the Access game. I was given a table with a field called
county. Values are 1-88 representing unique counties. I would like to
change these values to the actual county names. I'm at a loss as to how to
go about doing this. I would like the values changed permanently. Do I do
this in a query with a new variable, create another table with just county
names and join the two or what? I would really appreciate any and all help.

Mike

 

[Klatuu]

Other than the county numbers, what is in that table? does it include a
county number and the county name?

If so, then I would use that table as a child table to whatever table you
want to maintain county information for and store the county number. Then
you can create a relationship based on the county number field.

 

[Ed Warren]

Sounds like the Country (number) is a Primary Key. Does the table also have
a text field that gives the country name in text?
if so leave the data as is and use queries to present the data in human
readable form (country name). The user should never be aware of the country
(number).

Ed Warren.

 

[Takeadoe]

This is deer harvest data so it includes things like date of harvest, method,
metal tag number, etc. There is nothing else in the database, with the
exception of COUNTY (which is a numeric variable) that identifies the county.
I created another "child" table as you say and in it placed two variables -
COUNTYNAME & COUNTYCODE. The former contained the name of the county
(Athens, Adams, etc) and a corresponding number (5, 1, etc.). Note that
neither COUNTY in my parent table or COUNTYCODE in my child table are primary
keys. I joined them using a select query and with the exception of the
records with missing values for COUNTY, the two tables appeared to join just
fine. I changed to an update and it would not update. I got a rather
lenghty nastygram from Access explaining why the update was not possible. If
you would like, I could send you the message. Realize that I may have been
doing the update wrong. In the "update to" line in my parent table I typed
the following: [countynames].[COUNTYCODE] - Perhaps I did something wrong
here. What about this switch function? It looks like it may hold some
promise.

Mike

 

[Takeadoe]

Hi Ed. Thank you for the reply. COUNTY is not a primary key. And no, there
is no text field that holds the county name. If you get a chance, take a
look at my earlier reply to the other post.

Mike

 

[Ed Warren]

Still I would probably keep the county number and use it as a primary key.
If I read your post correctly you are keeping records deer harvest. In most
counties more than one deer is killed in a given county, so you have a 1:m
relationship between counties and deer harvested ( unless you are only
keeping a summary total). But since you are the DB Administrator, you get
to make the really big decisions.

Best of luck,

Ed Warren.

 

[Bob Miller]

Did you join the two tables (please don't refer to a table as a
database, it is confusing) on COUNTY in your main table adn COUNTYCODE
in your county table? Try that and include COUNTYNAME from the county
table in the query.

This is deer harvest data so it includes things like date of harvest,
method,
metal tag number, etc. There is nothing else in the database, with
the
exception of COUNTY (which is a numeric variable) that identifies the
county.
I created another "child" table as you say and in it placed two
variables -
COUNTYNAME & COUNTYCODE. The former contained the name of the county
(Athens, Adams, etc) and a corresponding number (5, 1, etc.). Note
that
neither COUNTY in my parent table or COUNTYCODE in my child table are
primary
keys. I joined them using a select query and with the exception of
the
records with missing values for COUNTY, the two tables appeared to join
just
fine. I changed to an update and it would not update. I got a rather
lenghty nastygram from Access explaining why the update was not
possible. If
you would like, I could send you the message. Realize that I may have
been
doing the update wrong. In the "update to" line in my parent table I
typed
the following: [countynames].[COUNTYCODE] - Perhaps I did something
wrong
here. What about this switch function? It looks like it may hold
some
promise.

Mike

:

Other than the county numbers, what is in that table? does it include
a
county number and the county name?

If so, then I would use that table as a child table to whatever table
you
want to maintain county information for and store the county number.
Then
you can create a relationship based on the county number field.

:

Brand New to the Access game. I was given a table with a field
called
county. Values are 1-88 representing unique counties. I would like
to
change these values to the actual county names. I'm at a loss as to
how to
go about doing this. I would like the values changed permanently.
Do I do
this in a query with a new variable, create another table with just
county
names and join the two or what? I would really appreciate any and
all help.

Mike