Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: TOP "n" User-Selected

    I have two queries.

    Query 1 has a list of projects and hourly allocations, restricted to the "TOP 10" hourly allocations.

    Query 2 uses the results from Query 1 to restrict a list of projects, resources, and hourly allocations of each. It wouldn't work to restrict Query 2, because it has more than 10 results... as it is, the TOP factor is working just the way I want it to.

    I have a report based upon Query 2, and my users call this report from a form.

    What I want to know is, is it possible to have the user select the report from the form, and then be given a list of choices for the TOP factor?

    For example, the user picks "Report TOPS blah blah" from the form, another form pops up with a listbox saying, "Pick one of the following options:

    Top 5 Values
    Top 10 Values
    Top 25 Values
    Top 5%
    Top 10%"

    From which they pick one, hit a button, and then the report shows up.

    I know the mechanics of offering them the choice, I just don't know how to plug the resulting change into the SQL for Query 1. Obviously the Top Values box isn't accepting "=Forms![Form1]![topnum]" or anything like that...

    Thanks in advance.

  2. #2
    Join Date
    Feb 2005
    Are you running the query using sql? I think that this is the only way to do what you want. My SQL is pretty weak so I make the query using Access and view the SQL. Then I copy and paste the SQL into my VBA. You'll need to use a string as a variable to change your range. (ex. strRange = "TOP 5"). So your sql will look something like this. strSql = "SELECT" & strRange & "myTable.* ...." . Now make this the row source of your report and your done.

Posting Permissions

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