Calculation help!

[Guest]

is this possible in access reports? it's complicated to explain, so hopefully
this will make sense.

i have a report that pulls the following:
For 3 different groups (i'll call them G1, G2, G3) by year, different sets
of info like Avg Price, Qty, Amount, etc. The Avg Price is a calculated field.

What I'm trying to do is this:
((Avg Price of G1 + Avg Price G2)/2)/(avg Price of G3) = x
*Note: the first parenthesis is actually just the AVG of the 2 Avg Prices -
not sure if you can do =AVG like in excel
Then
(Amount of G3/0.4)+((Amount of G1+Amt G2)/(x*.4))
*Note: I already have a calculation for the (Amt G1 + amt G2) so can just
pull that field.

ha...i hope this makes sense! Thanks!

 

[Guest]

There is a DAvg function in VBA. Take a look at it in VBA Help. You can use
domain aggragate functions in reports.

 

[Guest]

Unfortunatley, I don't really know code/VBA. Is there any way that you can
create an equation/formula in a text box on the report Design View?

 

[Guest]

Yes, that is what I was trying to say. You would write your formula pretty
much as you did in your original post using the DAvg the same way you would
in Excel, except you have to use the arguments specific to the database
table. It should looks something like:
((Avg Price of G1 + Avg Price G2)/2)/(avg Price of G3) = x
=((DAvg("[G1]", "SomeTable",Criteria) + DAvg("[G2]", "SomeTable",Criteria))
/ 2) / DAvg("[G3]","SomeTable,Criteria)

In the above example all the Gn references would be fields in your table,
SomeTable would be the name of the table, and Criteria would be where you
would put any filtering.