Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unanswered: Adding Longitude and Latitude

    I have one table with city and state, and another one with city, state, longitude and latitude. I joined on city. How do I set it up so that I don't get a new record for every incidence of city for various states?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Make a unique query where city is joined with city and make sure city, (and state?), longitude and latitude are the only fields to show in the query.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your query has to join on both the city name and the state
    ...just to avoid potential pitfalls I'd suggest you convert each element to uppercase on doing the join just in case say its NEW YORK in one table and New York in the other

    select Cities.City, City.State,CityLocations.Latitude, CityLocations.Longitude from Cities left join (CityLocations on ucase(CityLocations.City) = ucase(Cities.City) AND ucase(CityLocations.State) = ucase(Cities.State))
    order by Cities.State, Cities.City

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Healdem is correct - yes you need to also join on state. Sorry about that.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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