getting players records vs. other players

[pat67]

Hi I am having trouble doing this. Here is what i have. I have a table
that shows the date, winner, and loser. I then have 2 queries that
counts the number of times Player A won vs. Player B, and then how
many times Player A lost to Player B. I then combine thsoe 2 queries
and it works fine provided Player A and Player B have at least 1 win
and loss vs each other. If not, nothing shows up when i combine the
queries So what I need is for it to show that Player A is 4-0 vs
Player B and subsequently Player B is 0-4 vs Player A. Can someone
help?

example

Player Team Won Opponent Op_Team
Pat A 1 Al B
Pat A 1 Joe B
Joe B 1 Pat A

when i combine i get this

Player Team Opponent Team Won Lost
Pat A Joe B 1 1
Joe B Pat A 1 1

what I need is this

Player Team Opponent Team Won Lost
Pat A Joe B 1 1
Pat A Al B 1 0
Joe B Pat A 1 1
Al B Pat A 0 1

Thanks

 

[vanderghast]

Make an UNION query:


SELECT player, opponent, 1 as win, 0 as lost FROM table
UNION ALL
SELECT opponent, player, 0, 1 FROM table


save it, say, under that name qu.

Make a new query:

SELECT player, opponent, SUM(win), SUM(lost)
FROM qu
GROUP BY player, opponent





Vanderghast, Access MVP

 

[pat67]

Make an UNION query:

SELECT player, opponent, 1 as win, 0 as lost FROM table
UNION ALL
SELECT opponent, player, 0, 1 FROM table

save it, say, under that name qu.

Make a new query:

SELECT player, opponent, SUM(win), SUM(lost)
FROM qu
GROUP BY player, opponent

Vanderghast, Access MVP











- Show quoted text -

Thanks for the reply but that will not work.

Here is my actual query that shows the wins vs an opponent

SELECT tblResults.Winner AS Player, tblResults.Team AS PlayerTeam,
Count(tblResults.Winner) AS Won, tblResults.Loser AS Opponent,
tblResults.Team1 AS Op_Team
FROM tblResults
GROUP BY tblResults.Winner, tblResults.Team, tblResults.Loser,
tblResults.Team1;

That query gets me results like this

Player PlayerTeam Won Opponent Op_Team
Aaron Winklespecht ICAC-A 1 Al Afflerbach ICAC-B
Aaron Winklespecht ICAC-A 1 Bill Zeoli Doms B
Aaron Winklespecht ICAC-A 1 Bob Briggs Racks
Aaron Winklespecht ICAC-A 2 Bob Fries Kat B

I have a similar one that gets me losses vs. player

What happens is I cannot see any players that may have 0 wins vs
another player. Only show up if there are wins. Plus with your sql,
this player has 2 wins vs another player, but yours only shows it as
1.

 

[vanderghast]

If I start with the data you supplied:



WinLose ID Player Opponent
2 Pat Al
3 Pat Joe
4 Joe Pat



(other fields are irrelevant)


The union all query return:

Qu player opponent win lost
Pat Al 1 0
Pat Joe 1 0
Joe Pat 1 0
Al Pat 0 1
Joe Pat 0 1
Pat Joe 0 1




And the final query:

Query1 player opponent SumOfwin SumOflost
Al Pat 0 1
Joe Pat 1 1
Pat Al 1 0
Pat Joe 1 1


which shows Al who win no game.



Now, if Pat won twice over Al, the data to start with is:


WinLose ID Player Opponent
2 Pat Al
3 Pat Joe
4 Joe Pat
5 Pat Al



and the end result is


Query1 player opponent SumOfwin SumOflost
Al Pat 0 2
Joe Pat 1 1
Pat Al 2 0
Pat Joe 1 1



which correctly shows Pat having won two games over Al.


You probably did not copy what I suggested correctly, or your starting table does not have Pat winning twice over someone, or you fail to refresh the data view after you added the required record, or something else, that, from what I see, the suggested query works, here.



Vanderghast, Access MVP

 

[pat67]

I see no reason why vanderghast's method shouldn't work.  Translating it to
your table and column names, and combining it all into one query, with the
UNION ALL operation in a subquery try this:

SELECT Player, Team, Opponent, Op-Team,
SUM(Won) AS Wins, SUM(Lost) As Losses
FROM
    (SELECT Winner AS Player, Team,
     Loser AS Opponent, Team1 As Op-Team
     COUNT(*) As Won, 0 AS Lost
     FROM tblResults
     GROUP BY Winner, Team, Loser, Team1
     UNION ALL
     SELECT Loser, Team1,
     Winner, Team,
     0, COUNT(*)
     FROM tblResults
     GROUP BY Loser, Team, Winner, Team)
GROUP BY Player, Team, Opponent, Op-Team;

Ken Sheridan
Stafford, England

[quoted text clipped - 46 lines]

- Show quoted text -

Thanks for the reply but that will not work.

Here is my actual query that shows the wins vs an opponent

SELECT tblResults.Winner AS Player, tblResults.Team AS PlayerTeam,
Count(tblResults.Winner) AS Won, tblResults.Loser AS Opponent,
tblResults.Team1 AS Op_Team
FROM tblResults
GROUP BY tblResults.Winner, tblResults.Team, tblResults.Loser,
tblResults.Team1;

That query gets me results like this

Player                  PlayerTeam  Won     Opponent              Op_Team
Aaron Winklespecht  ICAC-A  1       Al Afflerbach    ICAC-B
Aaron Winklespecht  ICAC-A  1       Bill Zeoli           Doms B
Aaron Winklespecht  ICAC-A  1       Bob Briggs      Racks
Aaron Winklespecht  ICAC-A  2       Bob Fries             Kat B

I have a similar one that gets me losses vs. player

What happens is I cannot see any players that may have 0 wins vs
another player. Only show up if there are wins. Plus with your sql,
this player has 2 wins vs another player, but yours only shows it as
1.

Ken, thanks. But when I tried your sql, I got an error message saying
the select statement includes a reserved word or an argument name that
is misspelled or missing. or punctuation is incorrect. Sorry, I am a
novice at sql.

 

[pat67]

I see no reason why vanderghast's method shouldn't work.  Translatingit to
your table and column names, and combining it all into one query, with the
UNION ALL operation in a subquery try this:

SELECT Player, Team, Opponent, Op-Team,
SUM(Won) AS Wins, SUM(Lost) As Losses
FROM
    (SELECT Winner AS Player, Team,
     Loser AS Opponent, Team1 As Op-Team
     COUNT(*) As Won, 0 AS Lost
     FROM tblResults
     GROUP BY Winner, Team, Loser, Team1
     UNION ALL
     SELECT Loser, Team1,
     Winner, Team,
     0, COUNT(*)
     FROM tblResults
     GROUP BY Loser, Team, Winner, Team)
GROUP BY Player, Team, Opponent, Op-Team;

Ken Sheridan
Stafford, England

Make an UNION query:
[quoted text clipped - 46 lines]
- Show quoted text -
Thanks for the reply but that will not work.
Here is my actual query that shows the wins vs an opponent
SELECT tblResults.Winner AS Player, tblResults.Team AS PlayerTeam,
Count(tblResults.Winner) AS Won, tblResults.Loser AS Opponent,
tblResults.Team1 AS Op_Team
FROM tblResults
GROUP BY tblResults.Winner, tblResults.Team, tblResults.Loser,
tblResults.Team1;
That query gets me results like this
Player                  PlayerTeam  Won     Opponent              Op_Team
Aaron Winklespecht  ICAC-A  1       Al Afflerbach    ICAC-B
Aaron Winklespecht  ICAC-A  1       Bill Zeoli            Doms B
Aaron Winklespecht  ICAC-A  1       Bob Briggs      Racks
Aaron Winklespecht  ICAC-A  2       Bob Fries             Kat B
I have a similar one that gets me losses vs. player
What happens is I cannot see any players that may have 0 wins vs
another player. Only show up if there are wins. Plus with your sql,
this player has 2 wins vs another player, but yours only shows it as
1.

- Show quoted text -

Ken, thanks. But when I tried your sql, I got an error message saying
the select statement includes a reserved word or an argument name that
is misspelled or missing. or punctuation is incorrect. Sorry, I am a
novice at sql.- Hide quoted text -

- Show quoted text -

Hey Thanks to both of you. I figured it out. I used vanderghast's
union query directly from my table and not a query. that's what I was
doing wrong. Now it is working correctly.