Results 1 to 2 of 2
  1. #1
    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.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts