Weighted YTD Avg?

[BerkshireGuy]

I have the following data
Month/Year Rep Avg Courtesy Rating Number Returned
--------------------------------------------------------
May 2005 BJM 3.6 2
June 2005 BJM 3.2 3

I want to have a YTD weighted average -

So in this case, here is how I would calculate that:

Sum(Avg Rating * Number Returned)
---------------------------------
Total Number Returned

3.6*2+3.2*3 / 5 = YTD Total of 3.36

How can this be done in a query or report?

Thanks
Brian

[Ronald Roberts]

Brian,

Try this, I'm sure there are others who are better at SQL who
can show you a better method.

1.Create Query1
SELECT Table1.tRep, Year([tDate]) AS tYear, Sum(([tRate]*[tReturns])) AS
Total
FROM Table1 GROUP BY Table1.tRep, Year([tDate]);

This will give you the year extracted from the date so you can group be
rep id
and year. Also it calculates the sum of rate * returns which will be by
rep id and year.
2. Create Query2
SELECT Table1.tRep, Year([tDate]) AS tYear, Sum(Table1.tReturns) AS
SumOftReturns
FROM Table1 GROUP BY Table1.tRep, Year([tDate]);

This gives you the total number of returns by rep id and year.

3. Create Query3
SELECT Query1.tRep, Query1.tYear, Query1.Total, [Total]/[SumOftReturns]
AS WtAvg
FROM Query1 INNER JOIN Query2 ON (Query1.tYear = Query2.tYear) AND
(Query1.tRep = Query2.tRep);

This will join Query1 and Query2 by rep id and year and calculate the Wt
Avg by
Total / SumOftReturns

BerkshireGuy wrote:

I have the following data
Month/Year Rep Avg Courtesy Rating Number Returned
--------------------------------------------------------
May 2005 BJM 3.6 2
June 2005 BJM 3.2 3

I want to have a YTD weighted average -

So in this case, here is how I would calculate that:

Sum(Avg Rating * Number Returned)
---------------------------------
Total Number Returned

3.6*2+3.2*3 / 5 = YTD Total of 3.36

How can this be done in a query or report?

Thanks
Brian