Calculating a weighted average in a subform

M

Maury Markowitz

I need to calculate an average price for items in an order. For instance, a
typical order might look like...

15 @ 10
5 @ 7.5

In this case the average price is (15 * 10) + (5 * 7.5) / (10 + 5) = 9.375

I'm trying to duplicate this logic in a subform's footer. When I try it I
always get the entire row of footers turning into #Error. Here's what I
tried...

=(Sum([quantity*price])/Sum([quantity]))

Anyone know why this doesn't work? All the numbers are valid.
 
K

KARL DEWEY

=(Sum([quantity*price])/Sum([quantity]))
In this equation which price are you using? The 10 or the 7.5?

That is your problem.
 
J

Jeff Boyce

Maury

If you have 15 items @ $10 and 5 items @ $7.5, I believe you'd need to
divide by (15 +5)...

Your formula (=Sum...) looks to be referring to controls that are,
themselves, the results of calculations. If so, you can't do this in an
Access report. You need to, instead, refer all the way back to the original
detail record control(s).

And if you are referring to controls in another form (e.g., a subform), you
need to refer to it using correct syntax -- check Access HELP for
"expressions".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Maury Markowitz

KARL DEWEY said:
In this equation which price are you using? The 10 or the 7.5?

That is your problem.
Click to expand...

So are you saying that Access cannot do row-wise expressions?

How would I solve this problem then?

Maury
 
K

KARL DEWEY

No, you have to use queries or subqueries to complete your first equation --
average price is (15 * 10) + (5 * 7.5) / (10 + 5) = 9.375

Post your SQL and I or someone can show you how if you do not understand
what I mean.
 
M

Maury Markowitz

KARL DEWEY said:
No, you have to use queries or subqueries to complete your first equation --
average price is (15 * 10) + (5 * 7.5) / (10 + 5) = 9.375

Post your SQL
Click to expand...

The SQL works fine, that's not an issue. I'm trying to make an expression in
a field in a subform for display only.

Maury
 
K

KARL DEWEY

I do not see how it can be done in the form so that is why I asked for your
SQL.
 
Top