Avg, AS, GROUP BY query

[David McKnight]

I am re-posting this as I believe my previous thread has run dry.

I trying to get a weighted average from a union query. The jist of what I'm
trying to do is compare a teams average performance in games to how they are
ranked.

So far I have a union query of a query of games played with out comes
[Margin] and how each team is ranked.

My current approach works well when there are two records ie when
a team has played a game at home and away but not when the team has
only played one game or all as home or away. What I get in this senerio is
the product of the
two fields but not the average. I want to average
the ranking difference in the two teams.

So my query is currently written:


SELECT [ Team Performance Union].Season, [ Team Performance Union].[Home
Team], Avg([ Team Performance Union].[Avg Of Margin]*[Count Of Team
Performance]) AS Avg_Of_Margin, Avg([ Team Performance Union].[Avg Of
Ranking Diff]*[Count Of Team Performance]) AS Avg_Of_Ranking_Diff
FROM [ Team Performance Union]
GROUP BY [ Team Performance Union].Season, [ Team Performance Union].[Home
Team]
HAVING ((([ Team Performance Union].[Home Team])<>""));

Without the "HAVING" statment the query results included some identical
records except there is no "home
team" identified.

I do not have control of the orginal table design. If you think it would be
best to build the table first so that team is listed only once with another
field designating it as a home game or other I would have to build a query to
do so. Would like your get your insight on which is the best approach.

 

[[MVP] S.Clark]

I think you have the need for an Outer Join, based on the fact that some
team haven't completed a certain portion of the items. This typically means
that the join somewhere along the line is too restrictive. It appears that
you have a query of which this query is based. Revisit the joins in it.