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