query grouping issue

[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

 

[KARL DEWEY]

Post sample data with table and field names. Also post example of what you
want the results to look like.

 

[pat67]

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 -

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.

 

[KARL DEWEY]

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;

 

[pat67]

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 -

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

 

[KARL DEWEY]

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.

 

[pat67]

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 -

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