Need Help/Idea with making query work

[SilentReyn]

Good afternoon,
I'm having a bit of trouble figuring out how to make my query do exactly what I want and was hoping someone here would have an idea as to how I should go about this.

Here's what I have:
As the query stands, I enter a zip code, a radius in miles that I want to search, and a state -> the output shows me two columns: a job discipline (in this case, it's physical therapists, occupational therapists, etc) and the number of each for the values given. For this query, I know I am going to have to run it with the same zip code and state two more times - 10, 20, and 30 miles - and I do not foresee this changing anytime soon. My question is, how do I make the query, given a zip code and state only, show me the number of therapists per job discipline, per 10, 20, and 30 miles in one table? I have a beginner's level knowledge of SQL code. Below is the code that query currently uses:

SELECT Count(Prospects.JOB_FUNCTION) AS Total, Prospects.JOB_FUNCTION
FROM Z5LL INNER JOIN Prospects ON Z5LL.ZIP=Prospects.POSTAL
WHERE (((rtnx([Zip Code?],[zip]))<CInt([Miles?])) AND ((Z5LL.City)<>"APO" And (Z5LL.City)<>"FPO") AND ((Z5LL.ST)=[State?]))
GROUP BY Prospects.JOB_FUNCTION;

Prospects is the table housing all of the information and Z5LL is, I believe, the table that allows the radius search to work (I didn't write that bit). Please let me know if you need anymore information and I will be happy to assist. Thanks in advance for the help!
Reyn

[orange]

What functions are you using to get the distance between points? How do you identify points?
I have done something similar--- what companies supply certain products within X miles of some disaster point.

I had a database of ~60000 companies each with a known postal code; also all the products these companies supplied.

I needed a database that provided a Lat/Long coordinate (this was an approximate centroid for the postal code area). I was given a potential disaster at Point X, Y (lat/long); I could add and subtract a number from the Lat, and from the Long coordinate, to create a "logical box". Also you will need a function to calculate Great circle distance to calculate the distance between 2 points on the earth's surface; then process the points and save those that fall within the boundaries you have set. Not trivial.

Lots of info and Great circle details at http://www.cpearson.com/excel/latlong.aspx

Good luck.

[SilentReyn]

We are essentially using the same method as you with the lat/long coordinates. The Z5LL table houses a city, state, zip code, lat coords, long coords, etc. My assumption, since I didn't write the code for that table, is that the rtnx([Zip Code?],[zip])) is the radius calculator. All of this works as it should but I can't seem to figure out how to, basically, run the query 3 separate times with one execution to give me numbers for 10, 20, and 30 miles in one table. I can make 3 different queries and have them each run their respective miles but combining them into one table is the issue. I considered using a union query but I wasn't sure if that would be the best route. Again, I'm still learning on this and have been self taught this far. I appreciate all the help!

[orange]

I'm not sure what you are saving in your table, but could you set up a routine to run using the 10, 20 and 30 in one execution of the routine?


along this scheme

Proc (some parm)
run the 10 mile radius query
run the 20 mile radius query
run the 30 mile radius query
end proc

[June7]

So rtnx() is a custom function? This function returns a miles value? Is this value returned in increments of 10, 20, 30? Maybe include the function as a visible calculated field without filter criteria and then can see all records. Unfortunately, think that means calling the function twice because of GROUP BY. This might be very slow performance.

SELECT Count(Prospects.JOB_FUNCTION) AS Total, Prospects.JOB_FUNCTION, rtnx([Zip Code?],[zip]) As Miles
FROM Z5LL INNER JOIN Prospects ON Z5LL.ZIP=Prospects.POSTAL
WHERE Z5LL.City<>"APO" And Z5LL.City<>"FPO" AND Z5LL.ST=[State?]
GROUP BY Prospects.JOB_FUNCTION, rtnx([Zip Code?],[zip]);

Do you really need saved to a table or are you just wanting a workable query?

[SilentReyn]

I just ran the report showing the rtnx() function and it does appear that it is a custom function that returns a miles value from the entered zip code. I don't need to save the results directly from the query that runs - I have a separate excel sheet open when I run this query and I just manually enter the data that I'm given.

[June7]

If you have a report object present this data then there is no need to do the GROUP BY in the query. Use the the report Grouping & Sorting with aggregate calcs (Count) in group footers to accomplish.

[SilentReyn]

Output.zip
I apologize if I just don't understand this correctly but, if the report is going to show me the same data, why would I use it other than possibly speeding up performance? The way the data is shown to me now via the query results is just as I need. I need the query to run a total of three times, with the set values of 10, 20, and 30 miles, with one execution and then display the results to me broken down by job function in one column and each mile radius in it's own column - the attached file is an example of how I would like the output to be. Thanks for your patience and assistance!

[June7]

The query will not present miles data in multiple columns. All miles will be in one column. Need a CROSSTAB (not a UNION) to pivot into multiple columns.

Again, don't filter by miles. Allow the CROSSTAB to group and do the aggregate calc when it pivots the data.

[SilentReyn]

Ok, I'll give this a shot and post back once I've had time to tinker with this.