Help with calculation in group footer

Question

Hi,
This is my data.  It is grouped into months, and has a group footer where I sum the values (highlighted in red).  
For example, in the month footer I have controls of  
=Sum([TotalRecords])  [Lets call this A]
and 
=Sum([TotalSoftErrors]) [Lets call this B] 

But I now want a calculation in the group footer of B/A and I cannot seem to make sense of how to do it.

As you can see from the data, the current calculation is incorrect.
Your assistance would be greatly appreciated.

 

---

JG

Answer 1

But I now want a calculation in the group footer of B/A and I cannot seem to make sense of how to do it.

Hi John,

What about:    100 * Sum(TotalSoftErrors) / Sum(TotalRecords),   formatted in the right way?

Imb.

Answer 2

Set the control's ControlSource property to:

    =Sum([TotalSoftErrors] / [TotalRecords])

and set the control's Format property to 'Percent'.

---

Ken Sheridan, Stafford, England

Answer 3

Thanks Ken. You are my MVP. I wish I could nominate you. You have helped me so many times over the years.

Your solution wasn't actually what I was after, but it helped me none the less.

It was simply = [TotalSoftErrors] / [TotalRecords] what I was after, which I had tried, but because the numbers of one of the fields was so large, it appeared it wasn't working, but it was.

Thanks again. John, Melbourne Australia .... hiding inside from the coronavirus.

Stay safe.

---

JG

Answer 4

Hi Ken,

This is my current report, and I am trying to include a line chart that includes the calculated field error rate at the month aggregate level.

I have included the calculated field in the query,  and use that in the chart, but it wants me to SUM the percentages to chart them. Is there a method where I can included the monthly calculation in the chart where it doesn't aggregate anything? So I can chart the monthly error rate over time?

---

JG

Answer 5

I'm not very familiar with Access's charting features.  We've always used Excel for charting.  However, if the chart is based on the values returned in the report's query, rather than the base values which are then aggregated in the report (which my first reply assumed to be the case), then if you return the summation of a group of 1 (the month) then the value will be the original value.  In fact you could use any aggregation operator.

---

Ken Sheridan, Stafford, England

Answer 6

Thanks Ken.

Yeah I have never used the charting as it was always horrible. But it has improved a little and I'd thought I'd give it a go.  I understand what you are saying and will give it a go. I will try a group by and return the aggregate then *sum* the single figure.

 Thanks again. 

---

JG

Answer 7

Perfect,

Group by in the total row of the query editor for the Total records and Total Errors fields.

Then use Expression in the total row of the query editor (I was trying to use sum for a while and it was throwing errors) 

with the expression ErrorRate: Sum([TotalSoftErrors])/Sum([TotalRecords])

Produced gold. 

Many thanks again Ken. You are a star.

Regards

John

---

JG