convert numbers to 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

[Bob Quintal]

"Takeadoe" <mt********@msn.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:

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

The join is the right way to go. Using the join to relate the
county names in forms and reports is proper relational database
design. Importing the names to your main table is a bad thing to
do, as the text in many places wastes spece, allows spelling
errors to cause corrupted data, etc.

Access is very good in allowing you to use the countyID (number)
to retrieve the countyName (text).
--
Bob Quintal

PA is y I've altered my email address.

[pietlinden]

Say you have two tables:

CountryList(
CountryID,
CountryName)

OtherTable(
FieldX,
FieldY,
CountryID,
....)

So you need to add a TEXT field to "OtherTable" that's long enough to
store the longest country value. Call it "Country".

Then you can use something like this to update Country field in
OtherTable...

UPDATE tblCountryList INNER JOIN tblFixMe ON tblCountryList.CountryID =
tblFixMe.CountryID SET tblFixMe.CountryName =
[tblCountryList].[CountryName];
I know... now you're really confused because my names don't match up.
So focus on the SQL. If you copy and paste it into the QBE grid (hit
the SQL button first), you'll get the picture...

[MGFoster]

Takeadoe wrote:

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You would have to have a table (Counties) w/ both the number code and
the name of the county:

county_code county_name
1 Fairfield
2 Alameda
3 Contra Costa

.... etc. ...

If you wanted the code to be changed to the name, you'd have to be sure
the code column in the target table is a Text data type column. If not
you'd have to change it (in a query def SQL view enter & run this):

ALTER TABLE <table name> ALTER COLUMN county_code TEXT(40)

You can use whatever the maximum length of a county name is for the
number in TEXT parentheses.

Then you'd write an update query:

UPDATE <table name>
SET county_code = (SELECT county_name FROM Counties
WHERE county_code = <table name>.county_code)

Substitute your target table's real name for <table name>. Same for all
the column names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFfsPoechKqOuFEgEQIIHgCgvL7iepbIIn1RTIXoGv831I NR0h4An3hA
r1EaL8A5ns6EBhgM74kcp+50
=3Bhl
-----END PGP SIGNATURE-----