Help with Multiple Counts Query in Access 1

[pjammer1]

I am trying to develop an SQl Query that involves two tables. The VB6 program I am writing is basically for an office football pool and queries an Access 2002 database. It involves two tables, "Players" which has the players, the matchup ID and their respective picks for each game broken down each week. Here is a simplified version:

Week Player Matchup Pick
1 Joe Pats/Colts Pats
1 Mike Pats/Colts Colts
2 Joe Falcons/Chiefs Chiefs
2 Mike Falcons/Chiefs Falcons

The second table, "Matchups" contains each matchup and records the actual winner of each game and other stats, but for simplicity purposes, the fields would be as follows:

Week Matchup Actual Winner

1 Pats/Colts Pats
2 Falcons/Chiefs Chiefs

The result I am wanting is to show each players win/loss record by joining both tables. Thus far, I am able to get seperate results for Player and Player wins and Player and Player losses, but need to have one query that combines wins AND losses.

The query for wins is:

SELECT PICKS.PLAYER, COUNT (PICKS.PLAYER) as
WINS
FROM PICKS, MATCHUP
WHERE MATCHUP.MATCHUP = PICKS.MATCHUP
AND MATCHUP.WINNER = PICKS.WINNER
GROUP BY PICKS.PLAYER

Which gives the following:

Player Wins

Joe 2
Mike 0

The Query for losses is:

SELECT PICKS.PLAYER, COUNT (PICKS.PLAYER) as
LOSSES
FROM PICKS, MATCHUP
WHERE MATCHUP.MATCHUP = PICKS.MATCHUP
AND MATCHUP.WINNER <> PICKS.WINNER
GROUP BY PICKS.PLAYER

Player Losses

Joe 0
Mike 2

The actual result I need would be in this format

Player Wins Losses

Joe 2 0
Mike 0 2

Any help with developing a correct query would really be appreciated.

 

[dhookom]

I would try something like:

SELECT PICKS.PLAYER, Count(*) as MatchCount,
Sum(Abs(PICKS.WINNER = MATCHUP.WINNER)) as WINS,
Sum(Abs(PICKS.WINNER <> MATCHUP.WINNER)) as LOSSES
FROM PICKS INNER JOIN MATCHUP ON MATCHUP.MATCHUP = PICKS.MATCHUP
GROUP BY PICKS.PLAYER

Duane
Hook'D on Access
MS Access MVP

 

[pjammer1]

AWESOME!!! Exactly what I needed. Works perfect. Thanks so much for the quick reply and solution. I have been working on this for weeks.

Gary