Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL confusion... (2000)

    Hello, once again I'm having problems with some SQL statements. I am trying to set up a query that will act as a base for a radius search, but am having a problem with getting the lat/long of 2 different locations in the same query. Access's design view gave me this to start with:

    <code>
    SELECT qryDatHistory.*, tblZips.longitude AS sLong, tblZips.latitude AS sLat, tblZips.longitude AS cLong, tblZips.latitude AS cLat, qryDatHistory.Sstate, qryDatHistory.Scity, qryDatHistory.Cstate, qryDatHistory.Ccity
    FROM qryDatHistory LEFT JOIN tblZips ON (qryDatHistory.Cstate = tblZips.state) AND (qryDatHistory.Ccity = tblZips.city) AND (qryDatHistory.Sstate = tblZips.state) AND (qryDatHistory.Scity = tblZips.city);
    </code>

    This query will only display the lat/long if sstate=cstate and scity=ccity. I would like it to give the lat/long for each sstate-scity/cstate-ccity combination rather than only when they match. I don't know if this is possible without making another query that gets it's data from this one, but I hope it is possible because query speed might become a problem.

    Thanks,
    Ryan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL confusion... (2000)

    Delete the lines joining qryDatHistory and tblZips on Sstate vs state and Scity vs city (leave the other lines alone).
    Select Query | Show Table... or click the Show Table button on the toolbar.
    Add tblZips to the query; Access will give it an alias tblZips_1.
    Close the Show Table dialog.
    Create joins between qryDatHistory and tblZips_1 on Sstate vs state and Scity vs city.
    Add longitude and latitude from tblZips_1 to the query grid.
    Give them aliases of sLong and sLat (by prefixing the field names with the alias followed by a colon).

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL confusion... (2000)

    I don't think I understand. If you want to show all the lat/lon combinations by City and State, why are you using the qryDatHistory at all? It looks to me like a simple select query on tblZips would give you the result you want. You only use a join of any kind to filter information based on matching records. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  4. #4
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    qryDatHistory used to have all the info I needed for my queries until I decided to add radius as a criteria. qryDatHistory is pulling the Sstate/Scity/Cstate/Ccity from tblDatHistory and various company info from tblCompany_Information linked by an ID#. Whenever I perform the radius search I would like to just have to check one query instead of trying to get the info I need from 3 different tables, and I thought the best way to do this would be to query my exsiting query. As far as it being a join, that's because access made it for me automatically after I set up relationships the query became a join query??

    How about an example of how I could do this with a select query? I would like sLat/sLong to be for each sstate/scity, and cLat/cLong to be for each cstate/ccity.

    Hope that clears this up a bit. If not I'll try to explain what's happening again.

    Ryan

    Edit: I didn't see Han's post before I responed for some reason, I'll give it a shot and post back what I come up with.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    Thanks for the info Hans, the query now shows sLong/sLat/cLong/cLat as what appear to be the correct values however there is a new problem. The query is showing records multiple times which I think is from the left join. If I add DISTINCT to the select line the query seems to freeze at the very end(or it just takes forever I only waited about 5 minutes). Would it be better for me to the lat/long data to my DatHistory table so access doesn't have to calculate as much?

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL confusion... (2000)

    Do you need the Left joins?
    Have you set the new join correctly? The arrow should point to tblZips_1.

    I would avoid putting latitude/longitude data in the Dathistory table if possible, but I don't know enough about your database to give a definitive recommendation.

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    "Do you need the Left joins?" I'm asking myself the same question. The arrows are pointing the correct way. The left joins were created by access after I set the relationships, I don't know if I really need them or not. Here is the SQL I have for it now.

    <code>
    SELECT qryDatHistory.*, tblZips_1.longitude AS sLong, tblZips_1.latitude AS sLat, tblZips.longitude AS cLong, tblZips.latitude AS cLat
    FROM (qryDatHistory LEFT JOIN tblZips ON (qryDatHistory.Ccity = tblZips.city) AND (qryDatHistory.Cstate = tblZips.state)) LEFT JOIN tblZips AS tblZips_1 ON (qryDatHistory.Sstate = tblZips_1.state) AND (qryDatHistory.Scity = tblZips_1.city);
    </code>


    I have a feeling the duplicates are coming from the left joins, but I don't understand why. The reason I asked about puting the lat/long into tblDatHistory is just because I would like to avoid extra queries if they are not needed for speed purposes.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL confusion... (2000)

    Try changing the joins to inner joins (just replace LEFT with INNER in both instances), then see if you like the result.

  9. #9
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    I have actually tried that already and it seemed to give me more doubles than before. This is driving me nuts now. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks for the replys BTW, they are appreciated!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL confusion... (2000)

    I don't think we can do much without knowing more about the tables involved, or seeing (a cut-down copy of) the database.

  11. #11
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    Is it ok for me to send you a private message with a link to my DB? I can't post this to the public because of some of the company data stored in it. I'll remove as much data as I can from it and hopefully get it to under 2 meg zipped.

    Edit: Stripped it down from 900,000 dathistory records to only 3000 with only the required companies for the queries to work. It's now 1.26MB zipped. If anyone else would like to take a stab at it PM me for the link to the DB.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL confusion... (2000)

    See <post#=401925>post 401925</post#> for instructions on how to get a database down to postable size. You will have to replace sensitive data by dummy data.

  13. #13
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    I found out that my doubles are coming from tblZips here's an example of the data it holds:
    <code>
    zip latitude longitude city state
    44101 +41.523401 -081.599648 CLEVELAND OH
    44102 +41.472250 -081.740305 CLEVELAND OH
    44103 +41.518050 -081.642500 CLEVELAND OH
    44104 +41.480502 -081.629599 CLEVELAND OH
    44105 +41.452502 -081.632912 CLEVELAND OH
    44106 +41.504751 -081.602799 CLEVELAND OH
    44108 +41.543450 -081.608297 CLEVELAND OH
    44109 +41.445963 -081.698483 CLEVELAND OH
    44110 +41.564100 -081.571560 CLEVELAND OH
    44111 +41.459500 -081.787006 CLEVELAND OH
    </code>
    Each time my query finds a match for city/state it adds a record for each zipcode listed in tblZips. So Cleveland has about 15-20 zipcodes and If I wanted Cleveland to show up once in my query it will show it 15-20 times with each lat/lon for each zipcode. I wrote a function yesterday to remove the doubles from tblZips which does fix the problem, but I would rather keep all of the zipcode data if possible. Is there a way to tell access to only show one of the lat/lon combinations (at random maybe) for each city/state it's trying to match? If not I'll just use my chopped down version of tblZips.

    Again, Thanks for all the help

    Ryan

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL confusion... (2000)

    Instead of including qryDatHistory.* in your query, add the individual fields you need to the query grid.
    Change your query to a Totals query by selecting View | Totals or by clicking the Totals button on the toolbar.
    Leave the Total option for the fields from qryDatHistory as Group By, but set the Total option for sLong, sLat, cLong and cLat to First if you want a random value, or to Avg if you want to compute the average.

  15. #15
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL confusion... (2000)

    Thanks for the response, I tried your suggestion with both "first" and "average". Whenever I attempt to run either of them the query progress bar in the status bar moves to the last box and then stops. It might just be taking a while to gather the info but takes much too long regardless. I greatly appreciate all your help with this Hans, but it looks like my only other option now is to delete the doubles from tblZips.

    Edit: Chopped the query down to only include 2 rows(I will need all of them) from qryDatHistory the query took 2 minutes to complete [img]/forums/images/smilies/sad.gif[/img]. Does this sound like a good reason to purchase SQL Server?

Page 1 of 2 12 LastLast

Posting Permissions

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