Moving Average

  • Thread starter Thread starter David McKnight
  • Start date Start date
D

David McKnight

I would like to add three fields to the following Query that would create a
running or moving avg from the fields (columns 3, 4 & 5). The moving average
would avg the 10 previous season - not including current.
 
You can not average the averages and result in correct information.
You will need to sum all the raw data and divide by number of inputs.
--
KARL DEWEY
Build a little - Test a little


David McKnight said:
Sorry forgot query
SELECT DISTINCTROW [Season-Team-Coach before after Bowl Rankings].Season,
[Season-Team-Coach before after Bowl Rankings].Home, Avg([Season-Team-Coach
before after Bowl Rankings].[Delta Margin]) AS [Avg Of Delta Margin],
Avg([Season-Team-Coach before after Bowl Rankings].[Delta Win-Loss]) AS [Avg
Of Delta Win-Loss], Avg([Season-Team-Coach before after Bowl Rankings].[Delta
Log]) AS [Avg Of Delta Log], Count(*) AS [Count Of Season-Team-Coach before
after Bowl Rankings]
FROM [Season-Team-Coach before after Bowl Rankings]
GROUP BY [Season-Team-Coach before after Bowl Rankings].Season,
[Season-Team-Coach before after Bowl Rankings].Home
ORDER BY Avg([Season-Team-Coach before after Bowl Rankings].[Delta Log]) DESC;


--
David McKnight


David McKnight said:
I would like to add three fields to the following Query that would create a
running or moving avg from the fields (columns 3, 4 & 5). The moving average
would avg the 10 previous season - not including current.
Click to expand...
Click to expand...
 
season is the year, ie 2008, in which the season began. So moving avg for
[2008]should be data from 1997-2007.
[Avg of..] are numbers. not sure why I can average them as Karl Dewey
indicates - maybe within seperate query?

--
David McKnight


Clifford Bass said:
Hi David,

What does your Season column contain? Is there a way given a
particular value to determine the prior ten seasons' values?

Clifford Bass

David McKnight said:
Sorry forgot query
SELECT DISTINCTROW [Season-Team-Coach before after Bowl Rankings].Season,
[Season-Team-Coach before after Bowl Rankings].Home, Avg([Season-Team-Coach
before after Bowl Rankings].[Delta Margin]) AS [Avg Of Delta Margin],
Avg([Season-Team-Coach before after Bowl Rankings].[Delta Win-Loss]) AS [Avg
Of Delta Win-Loss], Avg([Season-Team-Coach before after Bowl Rankings].[Delta
Log]) AS [Avg Of Delta Log], Count(*) AS [Count Of Season-Team-Coach before
after Bowl Rankings]
FROM [Season-Team-Coach before after Bowl Rankings]
GROUP BY [Season-Team-Coach before after Bowl Rankings].Season,
[Season-Team-Coach before after Bowl Rankings].Home
ORDER BY Avg([Season-Team-Coach before after Bowl Rankings].[Delta Log]) DESC;


--
David McKnight


David McKnight said:
I would like to add three fields to the following Query that would create a
running or moving avg from the fields (columns 3, 4 & 5). The moving average
would avg the 10 previous season - not including current.
Click to expand...
Click to expand...
Click to expand...
 
Hi David,

Actually the prior ten of 2008 is 1998-2007. Just so you are not
puzzled by my following solution maybe not working correctly. I think Karl
may have misunderstood something. Or I am. Anyway, if you do the following
you will be able to generate information that covers all of the years you
have. It presumes that there no missing years or if there are any, the
10-year span will be figured as if they were there. For years where there
are not 10 years, such as in the first 10 years worth of seasons, it will
report as many as it can. It also gives you the starting and ending ranges
of the "10-year" span.

Your query, simplified by using a table alias and by removing the sort.
The sort is not needed at this point. I named it "qrySeason Summaries":

SELECT A.Season, A.Home, Avg(A.[Delta Margin]) AS [Avg of Delta Margin],
Avg(A.[Delta Win-Loss]) AS [Avg of Delta Win-Loss], Avg(A.[Delta Log]) AS
[Avg of Delta Log], Count(*) AS [Count of Season-Team-Coach before after Bowl
Rankings]
FROM [Season-Team-Coach before after Bowl Rankings] AS A
GROUP BY A.Season, A.Home;

Create a query named "qrySeasons" to get a distinct listing of the
seasons:

SELECT DISTINCT [Season-Team-Coach before after Bowl Rankings].Season
FROM [Season-Team-Coach before after Bowl Rankings];

A query named "qry10-Season Summaries" which calculates the 10-year
running averages (this deals with Karls point):

SELECT A.Season, B.Home, Min(B.Season) AS [10-Year Range Start],
Max(B.Season) AS [10-Year Range End], Avg(B.[Delta Margin]) AS [10-Year Avg
of Delta Margin], Avg(B.[Delta Win-Loss]) AS [10-Year Avg of Delta Win-Loss],
Avg(B.[Delta Log]) AS [10-Year Avg of Delta Log], Count(*) AS [10-Year Count
of Season-Team-Coach before after Bowl Rankings]
FROM qrySeasons AS A, [Season-Team-Coach before after Bowl Rankings] AS B
WHERE (((B.Season) Between [A].[Season]-10 And [A].[Season]-1))
GROUP BY A.Season, B.Home;

Put them together to get the current summaries and the prior 10-year
summaries:

SELECT A.Season, A.Home, A.[Avg of Delta Margin], A.[Avg of Delta Win-Loss],
A.[Avg of Delta Log], A.[Count of Season-Team-Coach before after Bowl
Rankings], B.[10-Year Range Start], B.[10-Year Range End], B.[10-Year Avg of
Delta Margin], B.[10-Year Avg of Delta Win-Loss], B.[10-Year Avg of Delta
Log], B.[10-Year Count of Season-Team-Coach before after Bowl Rankings]
FROM [qrySeason Summaries] AS A LEFT JOIN [qry10-Season Summaries] AS B ON
(A.Home = B.Home) AND (A.Season = B.Season)
ORDER BY A.[Avg of Delta Log] DESC;

Hope that does what you want.

Clifford Bass

David McKnight said:
season is the year, ie 2008, in which the season began. So moving avg for
[2008]should be data from 1997-2007.
[Avg of..] are numbers. not sure why I can average them as Karl Dewey
indicates - maybe within seperate query?
Click to expand...
 
season is the year, ie 2008, in which the season began. So moving avg for
[2008]should be data from 1997-2007.
[Avg of..] are numbers. not sure why I can average them as Karl Dewey
indicates - maybe within seperate query?
Click to expand...

Do you mean that you have *FIELDS* named [2008], [2007] and so on? If so...
your data structure is wrong and it will be much harder to get any sort of
average, running or not.
 
Worked perfectly!
--
David McKnight


Clifford Bass said:
Hi David,

Actually the prior ten of 2008 is 1998-2007. Just so you are not
puzzled by my following solution maybe not working correctly. I think Karl
may have misunderstood something. Or I am. Anyway, if you do the following
you will be able to generate information that covers all of the years you
have. It presumes that there no missing years or if there are any, the
10-year span will be figured as if they were there. For years where there
are not 10 years, such as in the first 10 years worth of seasons, it will
report as many as it can. It also gives you the starting and ending ranges
of the "10-year" span.

Your query, simplified by using a table alias and by removing the sort.
The sort is not needed at this point. I named it "qrySeason Summaries":

SELECT A.Season, A.Home, Avg(A.[Delta Margin]) AS [Avg of Delta Margin],
Avg(A.[Delta Win-Loss]) AS [Avg of Delta Win-Loss], Avg(A.[Delta Log]) AS
[Avg of Delta Log], Count(*) AS [Count of Season-Team-Coach before after Bowl
Rankings]
FROM [Season-Team-Coach before after Bowl Rankings] AS A
GROUP BY A.Season, A.Home;

Create a query named "qrySeasons" to get a distinct listing of the
seasons:

SELECT DISTINCT [Season-Team-Coach before after Bowl Rankings].Season
FROM [Season-Team-Coach before after Bowl Rankings];

A query named "qry10-Season Summaries" which calculates the 10-year
running averages (this deals with Karls point):

SELECT A.Season, B.Home, Min(B.Season) AS [10-Year Range Start],
Max(B.Season) AS [10-Year Range End], Avg(B.[Delta Margin]) AS [10-Year Avg
of Delta Margin], Avg(B.[Delta Win-Loss]) AS [10-Year Avg of Delta Win-Loss],
Avg(B.[Delta Log]) AS [10-Year Avg of Delta Log], Count(*) AS [10-Year Count
of Season-Team-Coach before after Bowl Rankings]
FROM qrySeasons AS A, [Season-Team-Coach before after Bowl Rankings] AS B
WHERE (((B.Season) Between [A].[Season]-10 And [A].[Season]-1))
GROUP BY A.Season, B.Home;

Put them together to get the current summaries and the prior 10-year
summaries:

SELECT A.Season, A.Home, A.[Avg of Delta Margin], A.[Avg of Delta Win-Loss],
A.[Avg of Delta Log], A.[Count of Season-Team-Coach before after Bowl
Rankings], B.[10-Year Range Start], B.[10-Year Range End], B.[10-Year Avg of
Delta Margin], B.[10-Year Avg of Delta Win-Loss], B.[10-Year Avg of Delta
Log], B.[10-Year Count of Season-Team-Coach before after Bowl Rankings]
FROM [qrySeason Summaries] AS A LEFT JOIN [qry10-Season Summaries] AS B ON
(A.Home = B.Home) AND (A.Season = B.Season)
ORDER BY A.[Avg of Delta Log] DESC;

Hope that does what you want.

Clifford Bass

David McKnight said:
season is the year, ie 2008, in which the season began. So moving avg for
[2008]should be data from 1997-2007.
[Avg of..] are numbers. not sure why I can average them as Karl Dewey
indicates - maybe within seperate query?
Click to expand...
Click to expand...
 
Back
Top