Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Modifying a Query

    Hello All,

    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)
    FROM Table1
    WHERE Vacant="Occupied" AND CanvassNumber=0 AND Zone=[Please Enter the Desired Zone]) AS num1, (SELECT Count(Vacant)
    FROM Table1
    WHERE Vacant="Vacant" AND CanvassNumber=0 AND Zone=[Please Confirm the Desired Zone]) AS num2, num1/(num1+num2) AS num3
    FROM Table1;

    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.

    Thanks so much for your help.

  2. #2
    Join Date
    Apr 2012
    Posts
    28
    You can loop it in ms access as well using VBA.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Quote Originally Posted by ieuan View Post
    You can loop it in ms access as well using VBA.
    Can you? I don't think that you can loop under DoCmd.OpenQuery, which is what I would have to do to utilize a SELECT query in VBA, and DoCmd****nSQL doesn't work on SELECT queries.

    I'm not sure how that would work logistically.

Posting Permissions

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