DAvg

[David McKnight]

I would like a query to give me the average of teams opponets strength on a
week to week basis, ie, a team plays 19 weeks schedule each season his opp
at week #1 has a strength of 10, week # 2 = 11, week #3 = 12.

So Week 1 ave opp strength = 10 (10/1)
Week 2 ave opp strenght = 10.5 ((10+11)/2
etc..

My query now is written as :

SELECT [Strength of Schedule-4].*, [Strength of Schedule-4].Home, [Strength
of Schedule-4].Week, ((DAvg("[Avg Of Avg Of Home Modified Score]","Home='" &
[Home] & "' AND Season " & [Season] & " AND Week BETWEEN " & [Week]-19 & "
AND " & [Week]+0))) AS [Opp Margin M-Avg]
FROM [Strength of Schedule-4];

But this gives me a reduntant week and season columns and errors for [Opp
Margin M-Avg].

Note 19 weeks in season, season begins in week 35.

 

[Michel Walsh]

Assuming you have just one record per team per week:



SELECT a.team, a.week, AVG(b.modifiedScore)
FROM [Strength of Schedule-4] AS a INNER JOIN [Strength of Schedule-4] AS b
ON a.team = b.team
AND a.week >= b.week
GROUP a.team, a.week



should do. I also assumed each team as a record for each week.

It does not work for cases where weeks span over year (week number restart
to 1, and first and last week of the year MAY be incomplete week, week with
less than 7 days).


Vanderghast, Access MVP

 

[David McKnight]

Michel,

Thanks but I'm having trouble match up you code to my database - I'm sure
because I did not give you all info necessary. The tabel I'm using has four
fields:

[Season] [ Week] [Home] [Avg Of Avg Of Home Modified Score] ie,
2008 36 Team-A 10.0
2008 37 Team-A 10.5

I not sure how to interprete you code use of "a" and "b", Your "Team" equals
my "Home"

I don't understand use of Join as I'm only referencing one table - I don't
see what needs to be joined.

thanks



--
David McKnight

Assuming you have just one record per team per week:



SELECT a.team, a.week, AVG(b.modifiedScore)
FROM [Strength of Schedule-4] AS a INNER JOIN [Strength of Schedule-4] AS b
ON a.team = b.team
AND a.week >= b.week
GROUP a.team, a.week



should do. I also assumed each team as a record for each week.

It does not work for cases where weeks span over year (week number restart
to 1, and first and last week of the year MAY be incomplete week, week with
less than 7 days).


Vanderghast, Access MVP

I would like a query to give me the average of teams opponets strength on a
week to week basis, ie, a team plays 19 weeks schedule each season his
opp
at week #1 has a strength of 10, week # 2 = 11, week #3 = 12.

So Week 1 ave opp strength = 10 (10/1)
Week 2 ave opp strenght = 10.5 ((10+11)/2
etc..

My query now is written as :

SELECT [Strength of Schedule-4].*, [Strength of Schedule-4].Home,
[Strength
of Schedule-4].Week, ((DAvg("[Avg Of Avg Of Home Modified Score]","Home='"
&
[Home] & "' AND Season " & [Season] & " AND Week BETWEEN " & [Week]-19 &
"
AND " & [Week]+0))) AS [Opp Margin M-Avg]
FROM [Strength of Schedule-4];

But this gives me a reduntant week and season columns and errors for [Opp
Margin M-Avg].

Note 19 weeks in season, season begins in week 35.

 

[Michel Walsh]

SELECT a.home, a.[week], AVG(b.modifiedScore)
FROM [Avg Of Avg Of Home Modified Score] AS a
INNER JOIN [Avg Of Avg Of Home Modified Score] AS b
ON a.home= b.home
AND a.[week] >= b.[week]
GROUP a.home, a.[week]


"a" and "b" are just alias. See them like having two fingers, one called a
and one called b, and each finger can move independently, over the unique
list, your table, as long as the records they 'point' satisfy the condition:

a.home= b.home
AND a.[week] >= b.[week]



Vanderghast, Access MVP

Michel,

Thanks but I'm having trouble match up you code to my database - I'm sure
because I did not give you all info necessary. The tabel I'm using has
four
fields:

[Season] [ Week] [Home] [Avg Of Avg Of Home Modified Score]
ie,
2008 36 Team-A 10.0
2008 37 Team-A 10.5

I not sure how to interprete you code use of "a" and "b", Your "Team"
equals
my "Home"

I don't understand use of Join as I'm only referencing one table - I don't
see what needs to be joined.

thanks



--
David McKnight

Assuming you have just one record per team per week:



SELECT a.team, a.week, AVG(b.modifiedScore)
FROM [Strength of Schedule-4] AS a INNER JOIN [Strength of Schedule-4]
AS b
ON a.team = b.team
AND a.week >= b.week
GROUP a.team, a.week



should do. I also assumed each team as a record for each week.

It does not work for cases where weeks span over year (week number
restart
to 1, and first and last week of the year MAY be incomplete week, week
with
less than 7 days).


Vanderghast, Access MVP


message

I would like a query to give me the average of teams opponets strength
on a
week to week basis, ie, a team plays 19 weeks schedule each season his
opp
at week #1 has a strength of 10, week # 2 = 11, week #3 = 12.

So Week 1 ave opp strength = 10 (10/1)
Week 2 ave opp strenght = 10.5 ((10+11)/2
etc..

My query now is written as :

SELECT [Strength of Schedule-4].*, [Strength of Schedule-4].Home,
[Strength
of Schedule-4].Week, ((DAvg("[Avg Of Avg Of Home Modified
Score]","Home='"
&
[Home] & "' AND Season " & [Season] & " AND Week BETWEEN " & [Week]-19
&
"
AND " & [Week]+0))) AS [Opp Margin M-Avg]
FROM [Strength of Schedule-4];

But this gives me a reduntant week and season columns and errors for
[Opp
Margin M-Avg].

Note 19 weeks in season, season begins in week 35.