Answered by:
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!
kenFriday, 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],[306090Results])
"306090Results" 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 workaround 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]", "[306090Results]")
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]", "[306090Results]", "[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