Calculating a weighted average in a subform

[Guest]

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.

 

[Guest]

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

That is your problem.

 

[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

 

[Guest]

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

Sorry, updated one page and not the other...

Your formula (=Sum...) looks to be referring to controls that are,
themselves, the results of calculations.

No, these are the original fields in the main part of the form. Should I be
using form control names rather that data field names?

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".

No, not in this case anyway.

Maury

 

[Guest]

In this equation which price are you using? The 10 or the 7.5?

That is your problem.

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

How would I solve this problem then?

Maury

 

[Guest]

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.

 

[Guest]

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

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

 

[Guest]

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