I have a query in an Access database that I use to calculate a percentage of vacant properties in an area. The query requires a user-input Zone to evaluate the percentage for. Here is the query:
SELECT (SELECT Count(Vacant)
WHERE Vacant="Occupied" AND CanvassNumber=0 AND Zone=[Please Enter the Desired Zone]) AS num1, (SELECT Count(Vacant)
WHERE Vacant="Vacant" AND CanvassNumber=0 AND Zone=[Please Confirm the Desired Zone]) AS num2, num1/(num1+num2) AS num3
I want to modify this query so that rather than asking for a user-input Zone number, it will simply calculate the percentage for each Zone number. Is there a way to use an equivalent of # for each number (1-28) in the Zone field, or at least run the calculation for an array of numbers 1-28? I cannot figure out how to do something like this in Access; if this were a regular java program i would use a simple for loop to accomplish this. I know that I would also have to Select Zone and Group By Zone respectively, I'm just looking for a replacement for the user prompt.