# 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
Friday, May 8, 2020 4:34 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 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 Saturday, May 9, 2020 2:03 PM
Friday, May 8, 2020 11:44 PM
• Bingo!

Thanks Ken!

Saturday, May 9, 2020 2:03 PM