Calculated value in Report (Q1+Q2+2*Q3)/4 (1 Viewer)

Good morning! I am struggling with calculating a value for a report in Access. I have tried multiple methods, but I think I am overthinking the problem.

I have a report based on a query. The report is grouped by Location. Each Location has a test result for each of 4 quarters. In the location footer I would like to calculate a value that is (Q2 + Q3 + 2*Q4)/4. This number represents a "look ahead" as to whether or not the test results may be out of compliance.

Any thoughts of how I can accomplish this? I am fairly comfortable with VBA, and I think I need to run code to make this work in the Location footer (?). But as of yet I have not had much success... If someone could point me in the right direction I would be grateful!

Cheers and Happy Holidays!
Jason

I'm not at a computer that has access at the moment, but I think you should be able to do this without vba. Are the "Q's" in the detail section of your report, or are only part of the record source of your report. Are the Q's in your above formula a summation of Q1, etc?

If so, you could try a text box in your footer and something like:

=(((Sum(Q1)+Sum(Q2+2))*Q3)/4

sxschech said:

I'm not at a computer that has access at the moment, but I think you should be able to do this without vba. Are the "Q's" in the detail section of your report, or are only part of the record source of your report. Are the Q's in your above formula a summation of Q1, etc?

If so, you could try a text box in your footer and something like:

=(((Sum(Q1)+Sum(Q2+2))*Q3)/4

Click to expand...

Thank you for your reply. I think I have come up with a similar solution that you have listed, but I had to restructure my data with an additional Query. It works, but it is a bit clumsy.

What I have is a Query (based on two other queries) that returns data structured as follows:
SampleDate (Date/Time)
LocationID (Text)
HAA5Total (Double)
TTHMTotal (Double)
Routine (y/n)

The report is then grouped on Location ID so that Running Annual Averages fop each location can be computed. Our regulating agency has now thrown the additional wrinkle of needing to compute the look ahead (the formula presented earlier) which I would very much like to include in the same report. However, with out the crosstab/transform data, I haven't been able to accomplish what I am hoping to do.

If you have an additional suggestion I would love to hear it. Otherwise, I think I will be implementing your suggestion utilizing an additional query.

Thanks again, Jm