Weighted Average

[mharvey]

I need to create a calculation in either a query or a
report that will give me a weighted average. Let' say I
have 5 items and each item has a dollar amount and a
volume of items per package, and I want to know the
average price per item. In Excel, the dollar amount is in
column a and the item count is in column b. The
calculation would be =sumproduct(a1:a5,b1:b5)/sum(B1:B5)
this would give me the average of the cost in cells
a1,a2,a3,a4 and a5 weighted against the values in cells
b1, b2, b3, b4 and b5. I just don't know how to do that
within an Access query or within a cell on an access
summary report. I have the table that includes the values
in column A and column B within each record. Can someone
help me?

 

[Arvin Meyer]

I'm not sure it can be done in a single query, but it can in a report or
several queries. Let's do it in a report. Set the controlsource of a textbox
to field for each of the 2 values in the detail section. In the footer, add
another textbox for each field and set their controlsources like:

= Sum(Field1)
and
= Sum(Field2)

then add a third textbox in the footer and set its controlsource:

= Sum(Field1)/Sum(Field2)

That should do it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[M.L. Sco Scofield]

There is a sample of how to do this in one of my presentation files.

Go to http://www.scobiz.com/Presentations.asp and download the July 2000
Queries 201 Total and Crosstab presentation file.

Although the mdb file is in 97 format, it converts to 2000 format just fine.

Look for query qtotBeanieAvgPrices in the mdb file which shows how to do
this.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Doug Munich]

If you create a query in design view, add the two columns, then click the
sigma button and for Total, choose Sum under these columns you get the
totals. Save the query. Then you click in the field area in the third
column, use the magic wand expression builder to create the expression
'SumOfColumnA / SumOfColumnB' (these values are available in the expression
builder under the query you are working on) and in the Total area back in
the query designer choose Expression under this column.

In SQL view it should look like:

SELECT Sum(tblWeightedAverage.Price) AS SumOfPrice,
Sum(tblWeightedAverage.Quantity) AS SumOfQuantity,
[SumOfPrice]/[SumOfQuantity] AS Expr1
FROM tblWeightedAverage;

Doug