Access Report Complicated Aggregate

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

Answer 1

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.

Answer 2

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

Answer 3

The arguments of DCount are strings:

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

---

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

Answer 4

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

Answer 5

Which field identifies the group? And what is its data type (number, text)?

---

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

Answer 6

Of course!  The "Clinician" field represents the group.  It's a text field, but I need to show an integer.

Thanks!

Answer 7

Perhaps

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

---

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

Answer 8

This is what I currently have:

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

Answer 9

The syntax of DCount is

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

---

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

Answer 10

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

Answer 11

Bingo!  

Thanks Ken!