query grouping issue

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Hello All,

I am running a pool league and I am trying to create a query to show
each player's record vs other players. By the same token, each team's
record vs other teams. I have a table that shows each week's results,
but I cannot figure out how to create the query I want. Any help would
be greatly appreciated. Thanks
 
Post sample data with table and field names. Also post example of what you
want the results to look like.
 
Post sample data with table and field names.  Also post example of whatyou
want the results to look like.
--
Build a little, test a little.






- Show quoted text -
Click to expand...

Ok here goes.

tblResults

Date Winner Team Runout Loser Team1
7/15/2009 Player A 123 Player B XXX
7/15/2009 Player A 123 Player B XXX
7/22/2009 Player B XXX Player A 123
7/22/2009 Player A 123 Player B XXX
7/29/2009 Player B XXX Player A 123
7/29/2009 Player A 123 Player B XXX

This is a small sample. What I am looking for is to be able to show
that Player A is 4 Wins and 2 losses against Player B. As well as the
team v team records and player v team.
 
Try these queries --
tblResults_Player --
SELECT tblResults.Winner, tblResults.Loser, Count(tblResults.Winner) AS
CountOfWinner
FROM tblResults
GROUP BY tblResults.Winner, tblResults.Loser;

tblResults_Team --
SELECT tblResults.Team, tblResults.Team1, Count(tblResults.Winner) AS
CountOfWinner
FROM tblResults
GROUP BY tblResults.Team, tblResults.Team1;

SELECT tblResults_Player.Winner, tblResults_Player.Loser,
tblResults_Player.CountOfWinner AS Wins
FROM tblResults_Player
UNION ALL SELECT tblResults_Team.Team, tblResults_Team.Team1,
tblResults_Team.CountOfWinner
FROM tblResults_Team;
 
Try these queries --
    tblResults_Player --
SELECT tblResults.Winner, tblResults.Loser, Count(tblResults.Winner) AS
CountOfWinner
FROM tblResults
GROUP BY tblResults.Winner, tblResults.Loser;

    tblResults_Team --
SELECT tblResults.Team, tblResults.Team1, Count(tblResults.Winner) AS
CountOfWinner
FROM tblResults
GROUP BY tblResults.Team, tblResults.Team1;

SELECT tblResults_Player.Winner, tblResults_Player.Loser,
tblResults_Player.CountOfWinner AS Wins
FROM tblResults_Player
UNION ALL SELECT tblResults_Team.Team, tblResults_Team.Team1,
tblResults_Team.CountOfWinner
FROM tblResults_Team;

--
Build a little, test a little.







- Show quoted text -
Click to expand...

Hi, thanks for the input, but these queries give me what I already
have. Wins by player, team etc. I can do get wins and losses that way.
What i cannot seem to do is get them in the same query that says
Player A has 4 wins and 2 losses vs. Player B. Can you help with that?

Thanks
 
The queries I posted produces this result --
Winner Loser Wins
Player A Player B 4
Player A Player C 1
Player B Player A 2
123 XXX 5
XXX 123 2

If this is not what you wanted then post an example of what you expect it ti
look like.
 
The queries I posted produces this result --
Winner  Loser   Wins
Player A        Player B        4
Player A        Player C        1
Player B        Player A        2
123     XXX     5
XXX     123     2

If this is not what you wanted then post an example of what you expect itti
look like.

--
Build a little, test a little.







- Show quoted text -
Click to expand...

You are exactly right, that's what is showing. What I am looking for
is something like this:

Player Opponent Wins Losses

Player A Player B 4 2
Player B Player A 2 4
 
Back
Top