Results 1 to 3 of 3
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: query quandry (A2K)

    It looks to me like you want to run a query that determines which queries have met their objective based on the amount, and then run a query that groups the results of that query. The idea of running a query on a query is one of Access' abilities that aren't immediately obvious, but can be very powerful. If you are familiar with SQL for other databases, it's sort of like a subquery. To give you much more help we would need more details on table structures and the amounts of data involved - for example are there several records for each objective, or is it pretty much one objective, one record?

    As an aside, it's often much easier to post the SQL string being used, as the images, especially embedded in a Word document, are difficult to read. I finally resorted to zooming the image back to nearly it's original size so I could read field names and so on.
    Wendell

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query quandry (A2K)

    Re the numbers or records: I have 368 consumers each enrolled in one or more of 6 different programs. Each of those programs has 7-12 objectives each. Of those, less than 20% have an amount that needs to be evaluated against a goal.

    So, query1 takes care of 80% or so of the records I
    Attached Images Attached Images

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query quandry (A2K)

    The query attached (query1) [edited and reattached] counts approved objectives and achieved objectives and then calculates the percentage achieved. The criteria for approved and achieved is a Boolean field.

    I need to take this one step farther. Some of these objectives have an amount that needs to be evaluated against a goal to determine whether or not they have been achieved. Then need the average of amount for the achieved objectives.

    The amount to be evaluated is "Amount" in tblConsProgObj. The measure against which it is evaluated is the combination of the fields "OperatorID" and "GoalsID" in tblProgObj. (Operator is either < or > and Goal is the numeric value.) [has been edited]

    I could really use some guidance on how to proceed, I
    Attached Images Attached Images

Posting Permissions

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