winning streak query

[pat67]

game id is autonumbered so every time i enter a result, a new game id
is created. I changed the query to use game id and it really looks
like it works now. the top guy is showing 15. the problem was the
anything in January was showing up first because of the 1. I was able
to change the original table to show 09 for September, but when i ran
the myData query, it didn't pick it up that way. Do you know why?

The next question would be to get from the same tblResults, a player's
current streak, whether it's wins or losses.

i.e.
Player Streak
Bob Won 6
Jim Lost 3
Frank Won 2

I am a pain I know.

 

[vanderghast]

You were probably using string rather than date_time as DATA TYPE for that
field (check the table design).

Vanderghast, Access MVP

It is preferable to have date as date_time datatype rather than string. If
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.

You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.

Vanderghast, Access MVP

game id is autonumbered so every time i enter a result, a new game id
is created. I changed the query to use game id and it really looks
like it works now. the top guy is showing 15. the problem was the
anything in January was showing up first because of the 1. I was able
to change the original table to show 09 for September, but when i ran
the myData query, it didn't pick it up that way. Do you know why?

 

[pat67]

It is preferable to have date as date_time datatype rather than string. If
the data type is date_time, the format you use (regional setting or
otherwise) won't influence the sort.

You can use GameID if GameID is already unique, instead of the game
date_time_stamp, as long as GameID increases as the date_time also
increases. There is no need for GameID to be continuous, without holes in
the sequence of values, though.

Vanderghast, Access MVP

Just so you know, I have a union query from my original table showing
gameID, date, player, opponent, and result. either won or lost. I need
to rank the results by player and gameID to get what is the current
streak. I am unsure how to do that. Can you help? Thanks

 

[vanderghast]

You just need the records with GameID ( positive values, increasing as time
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.

SELECT PlayerID,
MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM data
GROUP BY PlayerID


saved as q1. I assume your original data is in table (or query) called
data. Then a second query:

SELECT a.PlayerID,
COUNT(c.gameID) AS actualWinStreak

FROM (ListOfPlayers AS a
LEFT JOIN data AS b
ON a.playerID = b.playerID)
LEFT JOIN q1 AS c
ON b.playerID = c.playerID
AND b.mgame < c.gameID

GROUP BY a.PlayerID

should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".



Vanderghast, Access MVP

 

[pat67]

You just need the records with  GameID ( positive values, increasing astime
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.

SELECT PlayerID,
    MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM  data
GROUP BY PlayerID

saved as q1.  I assume your original data is in table (or query) called
data. Then a second query:

SELECT a.PlayerID,
    COUNT(c.gameID)  AS actualWinStreak

FROM (ListOfPlayers AS a
    LEFT JOIN data AS b
        ON a.playerID = b.playerID)
    LEFT JOIN q1 AS c
        ON b.playerID = c.playerID
            AND b.mgame < c.gameID

GROUP BY a.PlayerID

should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".

Vanderghast, Access MVP

my data is in a union query qryUnion with the field Player and Result.
i have a tblRosters with a Player Name field. Can you substitue those
into your query so I don't screw it up?

 

[pat67]

You just need the records with  GameID ( positive values, increasing astime
progress) , PlayerID and if the player win (or lost) with the Boolean field
isWin.

SELECT PlayerID,
    MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM  data
GROUP BY PlayerID

saved as q1.  I assume your original data is in table (or query) called
data. Then a second query:

SELECT a.PlayerID,
    COUNT(c.gameID)  AS actualWinStreak

FROM (ListOfPlayers AS a
    LEFT JOIN data AS b
        ON a.playerID = b.playerID)
    LEFT JOIN q1 AS c
        ON b.playerID = c.playerID
            AND b.mgame < c.gameID

GROUP BY a.PlayerID

should return the actual winning streak for each player. I assumed there
that you have a table ListOfPlayers which supply a list of playerID, without
dups. It also use the same table (or query) used in the first query, "data",
and the first query, "q1".

Vanderghast, Access MVP

Let me show you my qryUnion example

GameID Player Oponnent Result
1 Bob Steve Won
2 Joe Frank Won
3 Jim Al Won
1 Steve Bob Lost
2 Frank Joe Lost
3 Al Jim Lost


Obviously it is much larger but you get the gist.What I am looking for
is this

Player W L Current Streak
Bob 1 0 Won 1
Joe 1 0 Won 1
Jim 1 0 Won 1
Steve 0 1 Lost 1
Frank 0 1 Lost 1
Al 0 1 Lost 1

Or something similar

 

[pat67]

You can rebuild a query finding the maximum date stamp (gameID, here) with a
lost,

----------------------------------------
SELECT playerID, MAX(gameID) AS mgame
FROM  data
WHERE NOT isWin
GROUP BY playerID
----------------------------------------

saved as qlatestLost

and counting the number of records coming after that date stamp, for that
player,

-------------------------------
SELECT playerID,
    COUNT(qlatestLost.PlayerID)  AS actualWinStreak
FROM data LEFT JOIN qlatestLost
    ON data.playerID = qlatestLost.playerID
    AND data.gameID > qlatestLost.mgame
-------------------------------

Sure, if a player has lost his last game, the actualWinStreak is 0, thanks
to the outer join and the COUNT(field) behavior,

but

there is a problem:  if a player has not lost a single game, qlatestLoast
will return nothing too and so, the final query will also return 0 for this
player. To correct that problem, we can modify the last query to  (untested)
:

---------------------------
SELECT playerID,

    iif(
         playerID IN(SELECT playerID FROM qlatestLost),
         COUNT(qlatestLost.PlayerID),
        (SELECT COUNT(*)
                FROM data as b
                WHERE b.playerID = a.playerID)
         )         AS actualWinStreak

FROM data AS a LEFT JOIN qlatestLost
    ON data.playerID = qlatestLost.playerID
    AND data.gameID > qlatestLost.mgame
------------------------------

or, much much less verbose, only modify the first query into:

-----------------------------
SELECT playerID, MAX( iif(isWin, -1, 1) * gameID ) AS mgame
FROM  data
GROUP BY playerID
----------------------------

and keep the second query unchanged. This modification simply mark the
winning games as negative (for the purpose of that query) so that if a
player never lost a game, the returned MAX will be negative and all the
games, for that player, will be counted by the final query, as we want. If
the player lost a game, the queries behave as before. So, less
modifications, but less  "self documented" , enven if the first solution
(modifying the second query) is hardly what I call 'self documented' either.

Vanderghast, Access MVP

the top query tells me the specified field playerID could refer to
more than one table in the from clause

 

[vanderghast]

SELECT PlayerID,
MAX( iif(Result="win", -1, 1) * gameID ) AS mgame
FROM data
GROUP BY PlayerID

saved as q1, then


SELECT a.PlayerID,
COUNT(c.gameID) AS actualWinStreak

FROM (ListOfPlayers AS a
LEFT JOIN data AS b
ON a.playerID = b.playerID)
LEFT JOIN q1 AS c
ON b.playerID = c.playerID
AND b.mgame < c.gameID

GROUP BY a.PlayerID



where ListOfPlayers is a query returning all players, once.



Vanderghast, Access MVP