Query to count game wins/losses

[Carl Rapson]

I have a table that stores the results of games played,
with the following fields:

Table: Games
------------
id - Autonumber
GameNo - Integer
Winner - Long, id of winner (from Teams)
Loser - Long, id of loser (from Teams)
Winning score - Integer
Losing score - Integer

Table: Teams
------------
id - Autonumber
Team Name - Text

This seems sufficiently normalized to me, since there will
always be a winner and loser in a game (I'm not worries
about ties) and so there shouldn't be any wasted space.

What I would like to do is produce a report like the
following:

Team Name #Played #Won #Lost
--------- ------- ---- -----
Team1 10 5 5
Team2 10 7 3

.... and so forth. Is this possible? I suspect there may be
a crosstab involved, but I'm confused about how crosstab
queries work and I haven't been able to figure it out.
Does anyone have any suggestions?

Thanks for any input,

Carl Rapson

 

[Dan]

-----Original Message-----
I have a table that stores the results of games played,
with the following fields:

Table: Games
------------
id - Autonumber
GameNo - Integer
Winner - Long, id of winner (from Teams)
Loser - Long, id of loser (from Teams)
Winning score - Integer
Losing score - Integer

Table: Teams
------------
id - Autonumber
Team Name - Text

This seems sufficiently normalized to me, since there will
always be a winner and loser in a game (I'm not worries
about ties) and so there shouldn't be any wasted space.

What I would like to do is produce a report like the
following:

Team Name #Played #Won #Lost
--------- ------- ---- -----
Team1 10 5 5
Team2 10 7 3

.... and so forth. Is this possible? I suspect there may be
a crosstab involved, but I'm confused about how crosstab
queries work and I haven't been able to figure it out.
Does anyone have any suggestions?

Thanks for any input,

Carl Rapson

.

I would create 3 separate queries.

-query for number of games won

SELECT Games.Winner, Count(Games.Winner) AS [#Won]
FROM Games
GROUP BY Games.Winner;

-query for number of games lost

SELECT Games.Loser, Count(Games.Loser) AS [#Lost]
FROM Games
GROUP BY Games.Loser;

-final query with desired results

SELECT Teams.Team, ([#Won]+[#Lost]) AS [#Played], Winner.
[#Won], Loser.[#Lost]
FROM (Teams INNER JOIN Loser ON Teams.id = Loser.Loser)
INNER JOIN Winner ON Teams.id = Winner.Winner;

Good Luck,
Dan

 

[Carl Rapson]

Thank you, that was exactly what I needed.

Carl Rapson

-----Original Message-----

-----Original Message-----
I have a table that stores the results of games played,
with the following fields:

Table: Games
------------
id - Autonumber
GameNo - Integer
Winner - Long, id of winner (from Teams)
Loser - Long, id of loser (from Teams)
Winning score - Integer
Losing score - Integer

Table: Teams
------------
id - Autonumber
Team Name - Text

This seems sufficiently normalized to me, since there will
always be a winner and loser in a game (I'm not worries
about ties) and so there shouldn't be any wasted space.

What I would like to do is produce a report like the
following:

Team Name #Played #Won #Lost
--------- ------- ---- -----
Team1 10 5 5
Team2 10 7 3

.... and so forth. Is this possible? I suspect there may be
a crosstab involved, but I'm confused about how crosstab
queries work and I haven't been able to figure it out.
Does anyone have any suggestions?

Thanks for any input,

Carl Rapson

.

I would create 3 separate queries.

-query for number of games won

SELECT Games.Winner, Count(Games.Winner) AS [#Won]
FROM Games
GROUP BY Games.Winner;

-query for number of games lost

SELECT Games.Loser, Count(Games.Loser) AS [#Lost]
FROM Games
GROUP BY Games.Loser;

-final query with desired results

SELECT Teams.Team, ([#Won]+[#Lost]) AS [#Played], Winner.
[#Won], Loser.[#Lost]
FROM (Teams INNER JOIN Loser ON Teams.id = Loser.Loser)
INNER JOIN Winner ON Teams.id = Winner.Winner;

Good Luck,
Dan
.