locked
Access Report Complicated Aggregate RRS feed

  • Question

  • I have a report based on a query.  Here are the values of one column:

    Value
    =====
    97.00
    71.00
    69.00
    80.00
    87.00

    I'd like to provide an aggregate of this column in my report and know how to do a Sum, Average, etc.  However, I'd like to do something a bit more complicated and want to see if it's possible.  

    I'd like to know what percent of my values are >= 70.00.  So in the the above example, 4 of the 5 records were >= 70.00, so the value would be 80%.

    I think I know how to do this:

        1. Get an aggregate  of "=Count(*)"
        2. Get an aggregate  of "=Count(*) WHERE Value >= 70.00"
        3. Divide 2 by 3

    Question: is it possible to do a "=Count(*) WHERE Value >= 70.00"?

    Thanks!

    ken
    Friday, May 8, 2020 4:34 PM

Answers

  • I'd like to know what percent of my values are >= 70.00.  So in the the above example, 4 of the 5 records were >= 70.00, so the value would be 80%.

    In the Clinician group footer add a text box with a Control Source [property of:

        =Format(Sum(IIf([30 Days]>=70,1,0))/Count(*),"Percent")


    Ken Sheridan, Stafford, England

    • Marked as answer by Kenrav Saturday, May 9, 2020 2:03 PM
    Friday, May 8, 2020 11:44 PM

All replies

  • Question: is it possible to do a "=Count(*) WHERE Value >= 70.00"?

    Hi Ken,

    You can use the DCount function where you can supply criteria.

    See Help to study the function, and how to use it.

    Imb.

    Friday, May 8, 2020 4:50 PM
  • Imb,

    Thanks for your help.  I used the following (without criteria) for testing.  (I'll add criteria later.)

      =DCount([30 Days],[30-60-90-Results])

    "30-60-90-Results" is the underlying query.  I can save the report with no errors.

    However, I'm getting an "Enter Parameter Value" error when I launch the report.

    Any ideas?

    The good news is that I can work-around this issue by adding another column in my query, so no worries   However, your solution is much more elegant!

    Ken

    Friday, May 8, 2020 6:00 PM
  • The arguments of DCount are strings:

    =DCount("[30 Days]", "[30-60-90-Results]")


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, May 8, 2020 7:00 PM
  • Hans,

    Your solution is no longer throwing a query.  However, I neglected to mention that I'm grouping results by Employee.  So, as it stands right now, if Ken has 5 records, and Hans has 4 records, I"m seeing 9 records for the two Employee groups.  Probably need to limit my domain to just the group.  Can this be done?

    Ken

    Friday, May 8, 2020 7:50 PM
  • Which field identifies the group? And what is its data type (number, text)?

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, May 8, 2020 7:56 PM
  • Of course!  The "Clinician" field represents the group.  It's a text field, but I need to show an integer.

    Thanks!

    Friday, May 8, 2020 8:10 PM
  • Perhaps

    =DCount("[30 Days]", "[30-60-90-Results]", "[Clinician]='" & [Clinician] & "'")


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, May 8, 2020 8:13 PM
  • This is what I currently have:

      =DCount("[30 Days]","[Clinician]")

    Friday, May 8, 2020 8:20 PM
  • The syntax of DCount is

    DCount("FieldName", "TableorQueryName", "Condition")


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, May 8, 2020 8:35 PM
  • I'd like to know what percent of my values are >= 70.00.  So in the the above example, 4 of the 5 records were >= 70.00, so the value would be 80%.

    In the Clinician group footer add a text box with a Control Source [property of:

        =Format(Sum(IIf([30 Days]>=70,1,0))/Count(*),"Percent")


    Ken Sheridan, Stafford, England

    • Marked as answer by Kenrav Saturday, May 9, 2020 2:03 PM
    Friday, May 8, 2020 11:44 PM
  • Bingo!  

    Thanks Ken!

    Saturday, May 9, 2020 2:03 PM