Using a Union Query to get an average

[Guest]

I have two tables with simular information that I want to get and average
value when I combine. I have created a Union Query in which [Field 1] I have
the name I want to average based on. In [Field 2] I have the vaule I want to
average and [Field 3] I have the count. So ideally the query would calculate
the average value by:

(([Table 1]![Field 2]*[Table 3])+([Table 2]!Field 2*[Table 2]![Field
3]))/([Table 1]![Field 3]*[Table 2]![Field 3])

This calculation for distinct name in [Field 1].
Note I don't use an Average function because sometimes the count for one
table is zero (or not found) while in the other table there is a set of
records.

Is this clear what I'm trying to do?

Is there away to do it?

 

[Tom Ellison]

Dear David:

You say you have already created a UNION of the values, but then you proceed
to calculate the average using the two separate tables. What gives?

If you have the UNION already, with columns [Field 1], [Field 2], and [Field
3], then the average of [Field 2] for each group over [Field 1] would be:

SELECT [Field 1], Avg([Field 2]) AS Average
FROM UnionQuery
GROUP BY [Field 1]

If you need a weighted average by a count in [Field 3], then:

SELECT [Field 1], Avg([Field 2] * [Field 3]) AS Average
FROM UnionQuery
GROUP BY [Field 1]

In the above, substitute the actual name of your UnionQuery and the actual
field names.

The formula you propose doesn't look right. Would it be more like this:

([Table 1].[Field 2] * [Table 1].[Field 3])
+ ([Table 2].[Field 2] * [Table 2].[Field 3]) /
([Table 1].[Field 3] + [Table 2]![Field 3])

That might be correct if the tables have only one line for each [Field 1]
group. Otherwise, you will need some aggregate functions to perform the
consolidation over the groups.

Tom Ellison

 

[Dale Fye]

David,

Your description does not make much sense, to me.

Explain what your tables look like and what you are trying to accomplish
(use actual table and field names so when we post an answer, you won't have
to interpret what we have given you). Give a detailed explaination of the
data, so we have a better understanding regarding why you are not averaging.

From the sounds of your "Note", it seems like you may need to join the
tables, not create a union query.

Dale

 

[Guest]

Sorry for the criptic earlier message.

I have a tables [Home] and [Visitors] each are identical and have fields
[Team], [Avg Margin], [Count of Games].

Each team can be in the [Visitors] or the [Home] table or can play all games
at home all away.

My Union results in combining [Vistors] and [Home] into one field, an
additional fields [Avg Margin] and [count of games]. This union Query has one
or two records for each team that I want avg of avg margin based on the total
number games played at home and as a vistor.

I not sure the previous post from Tom addresses the problem I have if a team
does not have any records in one of the tables (ie, play all home games). I
have tried joining the tables but it does seem to work in with senerio.

David McKnight

David,

Your description does not make much sense, to me.

Explain what your tables look like and what you are trying to accomplish
(use actual table and field names so when we post an answer, you won't have
to interpret what we have given you). Give a detailed explaination of the
data, so we have a better understanding regarding why you are not averaging.

From the sounds of your "Note", it seems like you may need to join the
tables, not create a union query.

Dale

I have two tables with simular information that I want to get and average
value when I combine. I have created a Union Query in which [Field 1] I
have
the name I want to average based on. In [Field 2] I have the vaule I want
to
average and [Field 3] I have the count. So ideally the query would
calculate
the average value by:

(([Table 1]![Field 2]*[Table 3])+([Table 2]!Field 2*[Table 2]![Field
3]))/([Table 1]![Field 3]*[Table 2]![Field 3])

This calculation for distinct name in [Field 1].
Note I don't use an Average function because sometimes the count for one
table is zero (or not found) while in the other table there is a set of
records.

Is this clear what I'm trying to do?

Is there away to do it?

 

[Tom Ellison]

Dear David:

If you perform what I suggested on the UNION (as I also suggested) then it
will not matter if there are rows from only one of the two tables, or from
both.

For your future reference, it would work very well to have just one table
instead of the two. Just put a column in to show whether each row is for
home or visitor.

Tom Ellison

Sorry for the criptic earlier message.

I have a tables [Home] and [Visitors] each are identical and have fields
[Team], [Avg Margin], [Count of Games].

Each team can be in the [Visitors] or the [Home] table or can play all
games
at home all away.

My Union results in combining [Vistors] and [Home] into one field, an
additional fields [Avg Margin] and [count of games]. This union Query has
one
or two records for each team that I want avg of avg margin based on the
total
number games played at home and as a vistor.

I not sure the previous post from Tom addresses the problem I have if a
team
does not have any records in one of the tables (ie, play all home games).
I
have tried joining the tables but it does seem to work in with senerio.

David McKnight

David,

Your description does not make much sense, to me.

Explain what your tables look like and what you are trying to accomplish
(use actual table and field names so when we post an answer, you won't
have
to interpret what we have given you). Give a detailed explaination of
the
data, so we have a better understanding regarding why you are not
averaging.

From the sounds of your "Note", it seems like you may need to join the
tables, not create a union query.

Dale

message

I have two tables with simular information that I want to get and
average
value when I combine. I have created a Union Query in which [Field 1] I
have
the name I want to average based on. In [Field 2] I have the vaule I
want
to
average and [Field 3] I have the count. So ideally the query would
calculate
the average value by:

(([Table 1]![Field 2]*[Table 3])+([Table 2]!Field 2*[Table 2]![Field
3]))/([Table 1]![Field 3]*[Table 2]![Field 3])

This calculation for distinct name in [Field 1].
Note I don't use an Average function because sometimes the count for
one
table is zero (or not found) while in the other table there is a set of
records.

Is this clear what I'm trying to do?

Is there away to do it?

 

[Guest]

The weighted average approach works well when there are two records ie when
there a team has played a game at home and away but not when the team has
only played one game. What I get when in this senerio is the product of the
two but not the average. The query is alittle more complex than I had
described before in that it is also grouped by season and 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])<>""));

The query results included some identical records except there was 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.

 

[Guest]

I ment to say when a team has played only at Home or away but not both.
--
David McKnight

The weighted average approach works well when there are two records ie when
there a team has played a game at home and away but not when the team has
only played one game. What I get when in this senerio is the product of the
two but not the average. The query is alittle more complex than I had
described before in that it is also grouped by season and 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])<>""));

The query results included some identical records except there was 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.



--
David McKnight

I have two tables with simular information that I want to get and average
value when I combine. I have created a Union Query in which [Field 1] I have
the name I want to average based on. In [Field 2] I have the vaule I want to
average and [Field 3] I have the count. So ideally the query would calculate
the average value by:

(([Table 1]![Field 2]*[Table 3])+([Table 2]!Field 2*[Table 2]![Field
3]))/([Table 1]![Field 3]*[Table 2]![Field 3])

This calculation for distinct name in [Field 1].
Note I don't use an Average function because sometimes the count for one
table is zero (or not found) while in the other table there is a set of
records.

Is this clear what I'm trying to do?

Is there away to do it?