06-24-14, 08:39 #1Registered User
- Join Date
- Jun 2014
Unanswered: Help analyzing string within table
I am trying to do the following in Access and am a little rusty.
I have a table which has a bunch of records that have an abbreviation and a code. For example, I have records like this:
I'd like to look at each code and use the abbreviation to extract the city name and store it within the same table. For example, it would look at LAX5511 and in a different column it would list the city as Los Angeles and the same with LV2233 where it would list the city as Las Vegas. How can I do this and store the information within the same table? Please note that the code/numbers change so there could be 30 instances of LAX with different numbers after that, e.g. LAX5511, LAX5599, LAX5510 etc. etc.
Thanks in advance.
06-24-14, 09:19 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
don't store the city name, just look it up as required
have a table with the airport prefix and city name
then join to that table. or better yet defien a realtionship between the cities table and the other table
SELECT my, column, list, cities.CName from mytable
join cities on cities.AirportCode = left(mytable.FlightCode,3)I'd rather be riding on the Tiger 800 or the Norton