change field based on 2nd field

  • Thread starter Thread starter Rick Campbell
  • Start date Start date
R

Rick Campbell

I link to a database that has a city field and a zip code field. I run
statistics on the city field, but the zip field is more accurrate. In other
words, many times the city field is wrong. I would like to make a new table
that would change the city fields to reflect the city that the zip code
belongs to.

Can someone give me an idea of how to do this? I also need to keep all the
other fields in the database intact.

TIA

Rick
 
You would need a table of ZIPCodes and the corresponding City Names. Then you
could link on them. You might try SQL that looks like:

SELECT T.ZIPCODE, T.CityName
FROM Tablename As T
WHERE T.CityName = (
SELECT TOP 1 T1.CityName
FROM TableName As T1
WHERE T1.ZIPCode = T.ZIPCODE
ORDER BY Count(T1.CityName) DESC))

If that works as I think it will, you will end up with the most _**_popular_**_
value for the city name for each zip code. You can then use that to build a
lookup table.
 
Thanks, John!


John Spencer (MVP) said:
You would need a table of ZIPCodes and the corresponding City Names. Then you
could link on them. You might try SQL that looks like:

SELECT T.ZIPCODE, T.CityName
FROM Tablename As T
WHERE T.CityName = (
SELECT TOP 1 T1.CityName
FROM TableName As T1
WHERE T1.ZIPCode = T.ZIPCODE
ORDER BY Count(T1.CityName) DESC))

If that works as I think it will, you will end up with the most _**_popular_**_
value for the city name for each zip code. You can then use that to build a
lookup table.
 
Back
Top