How to access individual fields in a report to produce a grand total.

[Ironman]

Dear Access Developers:

I have an interesting problem that I hope someone could suggest how to
solve:

I have the following Report:

Col-1 Col-2
-------- -------
483.3 100
57.1 65
686.0 109
860.1 139
--------
2086.5

I need to show on the report a "special total" using the following
formula:

special total = 100 X (483.3/2086.5) + 65 X (57.1/2086.5) +
109 X (686.0/2086.5) + 139 X (860.1/2086.5)

If you notice, the formula requires access to individual values. How do
I refer to individual records/fields in a report??

I've been able to reproduce the formula using SQL in the query design
grid then pasting the SQL code into the Control Source of a text box
positioned in the report. I get a syntax error telling me that there are
too many ")", but the SQL code works perfectly when executed from the
Query Design grid. Here is the SQL code:

SELECT Sum([EFF]*([MIN]/(SELECT Sum([MIN]) FROM
test_700_Efficiency_qry;))) AS MyTot
FROM test_700_Efficiency_qry;
So my question is how do I retrieve individual values from a report or
is there a better approach to this problem instead of using SQL?

Thank you for any help!
Greg
*** Sent via Developersdex http://www.developersdex.com ***

[Gabriele Bertolucci]

> I have the following Report:

Col-1 Col-2
-------- -------
483.3 100
57.1 65
686.0 109
860.1 139
--------
2086.5
I need to show on the report a "special total" using the following
formula:

special total = 100 X (483.3/2086.5) + 65 X (57.1/2086.5) +
109 X (686.0/2086.5) + 139 X (860.1/2086.5)
If you notice, the formula requires access to individual values. How
do I refer to individual records/fields in a report??

So my question is how do I retrieve individual values from a report or
is there a better approach to this problem instead of using SQL?

I suggest you to avoid complexing your life with so complex calculated field,
and to use VBA to get all the values you want.
You may open a recordset in the page header format event, use it in the page
body format event to get all values you need, and finally to use those values
in the page footer format event to populate relative unbound fields.

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype: pbsoftsolution

[Ironman]

Thank you Gabriel for your suggestions. I'll look into doing that. I
welcome any further comments from anyone else on this issue.
Thanks...Greg
*** Sent via Developersdex http://www.developersdex.com ***