Complex GROUP BY query (1 Viewer)

[David R]

For once, I'm using Access for fun and not for profit...err, work.

I'm trying to query the game distribution on a site I'm at; There are seven players in each game, with 5 set roles (2 Mafia, 3 Townspersons) and two "optional": either the game contains a Doctor or it doesn't, and either the game contains a Cop or it doesn't. If either of those doesn't exist, they are additional Townspersons.

What I want to see is how having a Cop or Doctor factors into the outcome. Obviously there are four possible outcomes, the first two of which are fairly easy to test for:

• 1 Cop, No Doc;
• No Cop, 1 Doc;
• 1 Cop, 1 Doc;
• No Cop, No Doc;

How do I determine the latter two, though? Ideally I'd like to get all four possibilities in one final query that I can then tally for wins/losses, but if I have to write the final 8 stats by hand, that won't make me cry. The only thing I'm coming up with right now are some arcane union/exclusion queries, and I somehow doubt that's actually necessary...

My current queries are:

SELECT tableGames.GameResult, Count(tableGames.GameID) AS CountOfGameID
FROM tableGames LEFT JOIN tablePlayers ON tableGames.GameID = tablePlayers.GameID
WHERE (((tableGames.GameType)="C9 Newbie") AND ((tableGames.GameEnd) Is Not Null) AND ((tablePlayers.GameRole)=1))
GROUP BY tableGames.GameResult;

And the same thing, but with GameRole=3. Role 1 is Townie, and Role 4 is Mafia, if that matters.