Combining Query Results

[Bob Mullen]

I am using an Access table to track sports schedules and games results. I
have created queries that total the wins for each team, another that totals
the losses and another for ties. I would like to bring these queries together
to get what amounts to the league standings. Really I just need to add the
losses count and the ties count to the wins query. Each of these values
(wins, losses and ties) is based on a Count field in the respective query.

Any help would be appreciated.


Thanks in advance.

Bob Mullen

 

[John Spencer]

Care to share your table structure. Or at least the three queries.

If your structure was
TeamIdentifier
Result (Win, Loss, Tie)

Then you could use either a cross tab query or

SELECT TeamIdentifier
, Count(IIF(Result="Win",Result,Null)) as Wins
, Count(IIF(Result="Tie",Result,Null)) as Ties
, Count(IIF(Result="Loss",Result,Null)) as Losses
, Count(Result) as TotalGames
FROM YourTable
GROUP BY TeamIdentifier

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Bob Mullen]

Thanks John.

Here's what I have so far (it may not be the best data design). The primary
table Results has fields that identify the sport, game date, home team, home
score, visiting team, visitor score.

From this table I was able to construct a query that compared the game score
and identified the winning team by name (field Win), losing team by name
(field Loss), and for ties both Win and Loss are set to Tie. The query also
includes the home team name and visitor name for referencing in the event of
a tie.

So for instance, a record might look like this:

Sport=Junior Football
HTeam=HCSS
VTeam=SHS
Win=HCSS
Loss=SHS

or in the case of a tie

Sport=Junior Football
HTeam=HCSS
VTeam=SHS
Win=Tie
Loss=Tie

So what I would like to do is be able to summarize this data by sport (no
problem) and by team (calculating the number of wins, losses, ties and from
that Games Played and Points).

Hope this gives you a little better idea of what I am up to.

Thanks again.

Bob Mullen

 

[John Spencer]

That structure does make it tougher. I think the following MIGHT work to
give you results

Make a union query that looks like the following and save it as qHomeAway
SELECT HTeam as Team
, Abs(Sum(HTeam=Win)) as Wins
, Abs(Sum(HTeam=Loss)) as Losses
, Abs(Sum(Win="Tie")) as Ties
FROM Results
GROUP BY HTeam
UNION ALL
SELECT VTeam as Team
, Abs(Sum(VTeam=Win)) as Wins
, Abs(Sum(VTeam=Loss)) as Losses
, Abs(Sum(Loss="Tie")) as Ties
FROM Results
GROUP BY VTeam

Now use qHomeAway in a second query
SELECT Team
, Sum(Wins) as TotalWins
, Sum(Losses) as TotalLosses
, Sum(Ties) as TotalTies
FROM qHomeAway
Group By Team


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

Assuming that you've already created queries that calculate wins,
losses, and ties, as well as list the identifier for a team, you just
need to create a new query that uses those three queries, and join
them on the unique identifier, select the three fields and that's it.

Cheers,
Jason Lepack

 

[Bob Mullen]

Thanks very much for the help John. I got the query to work finally.

Here is my next question. Now that I have a query that calculates Games
Played, Wins, Losses, Ties and Points (ie: Wins * 2 + Ties), I need to enable
a ranking that will be done manually to take into account a rather
complicated set of tie-breaking procedures.

If I have a table that contains Sport, School and Rank as fields, is there
any way to combine these with the query result that also includes Sport,
School but also the calculated GamesPlayed, Wins, Losses, Ties, Points fields?

In the end, I am looking for a query that will produce the standings for the
sport(s) where Rank is included and used to sort in addition to the previous
query results.

Thanks again.

Bob Mullen

 

[John Spencer]

I guess this would be possible although you could not update the fields in
the query that would result from combining the table with the query.

Can you be more specific on why you need this? Do you need the query for a
report or do you want to update the table with sport school and rank while
displaying the results you have calculated for wins, losses, ties, games
played, and points?

SELECT YourTable.*, TheCalculatedQuery.*
FROM YourTable INNER JOIN TheCalculatedQuery
ON YourTable.Sport = TheCalculatedQuery.Sport
AND YourTable.School= TheCalculatedQuery.School

If you want to do data entry, then you are probably going to need to use a
form to show the information from your table with a subform (or possibly a
coordinated form) to show the information from the Calculated query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..