winning streak query

I have a table that has results on it with a winner and a loser. what
I am trying to do is get a winning or losing streak query based on
date and game id. meaning a player can play 4 times in one nite so i
would need to first look to date then to game id. so if he one the
first 3 and lost the last one he would have a winning streak of 3. I
hope i am explaining enough. Any ideas? Thanks

Hi,

Interesting question. Here is one way that presumes that three or more
games in a row of winning or losing out of an unlimited number of games on a
particular day consitutes a streak. It gives you the start and end game ID
and the number of games in the streak. It is broken down by day, player and
type of streak. As you did not give your table definition here is the one I
used:

tblGamesPlayers
GameDate
PlayerID
GameID
Winner (Yes/No field)

"qryWin Lose Streaks-Part 1" which determines each consecutive triplet of
wins and loses:

SELECT A.GameDate, A.PlayerID, A.Winner, A.GameID, B.GameID, C.GameID
FROM (tblGamesPlayers AS A INNER JOIN tblGamesPlayers AS B ON (A.Winner = B.
Winner) AND (A.PlayerID = B.PlayerID) AND (A.GameDate = B.GameDate)) INNER
JOIN tblGamesPlayers AS C ON (B.Winner = C.Winner) AND (B.PlayerID = C.
PlayerID) AND (B.GameDate = C.GameDate)
WHERE (((B.GameID)=(select Min(GameID) from tblGamesPlayers as D where D.
GameDate = A.GameDate and D.PlayerID = A.PlayerID and D.GameID A.GameID))
AND ((C.GameID)=(select Min(GameID) from tblGamesPlayers as E where E.
GameDate = B.GameDate and E.PlayerID = B.PlayerID and E.GameID B.GameID)));

"qryWin Lose Streaks-Part 2" which gives the desired information by
summarizing the above results:

SELECT Z.GameDate, Z.PlayerID, Z.Winner, Count(*)+2 AS GamesInStreak, Min(Z.A.
GameID) AS StreakStart, Max(Z.C.GameID) AS StreakEnd
FROM [qryWin Lose Streaks-Part 1] AS Z
GROUP BY Z.GameDate, Z.PlayerID, Z.Winner;

It might be possible to condense that all into one query. I leave that
to you to attempt if you wish.

Clifford Bass

pat67 wrote:
I have a table that has results on it with a winner and a loser. what
I am trying to do is get a winning or losing streak query based on
date and game id. meaning a player can play 4 times in one nite so i
would need to first look to date then to game id. so if he one the
first 3 and lost the last one he would have a winning streak of 3. I
hope i am explaining enough. Any ideas? Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201001/1

On Jan 4, 7:28*pm, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:
Hi,

* * *Interesting question. *Here is one way that presumes that three or more
games in a row of winning or losing out of an unlimited number of games on a
particular day consitutes a streak. *It gives you the start and end game ID
and the number of games in the streak. *It is broken down by day, player and
type of streak. *As you did not give your table definition here is the one I
used:

tblGamesPlayers
* * GameDate
* * PlayerID
* * GameID
* * Winner * (Yes/No field)

"qryWin Lose Streaks-Part 1" which determines each consecutive triplet of
wins and loses:

SELECT A.GameDate, A.PlayerID, A.Winner, A.GameID, B.GameID, C.GameID
FROM (tblGamesPlayers AS A INNER JOIN tblGamesPlayers AS B ON (A.Winner = B.
Winner) AND (A.PlayerID = B.PlayerID) AND (A.GameDate = B.GameDate)) INNER
JOIN tblGamesPlayers AS C ON (B.Winner = C.Winner) AND (B.PlayerID = C.
PlayerID) AND (B.GameDate = C.GameDate)
WHERE (((B.GameID)=(select Min(GameID) from tblGamesPlayers as D where D.
GameDate = A.GameDate and D.PlayerID = A.PlayerID and D.GameID A.GameID))
AND ((C.GameID)=(select Min(GameID) from tblGamesPlayers as E where E.
GameDate = B.GameDate and E.PlayerID = B.PlayerID and E.GameID B.GameID)));

"qryWin Lose Streaks-Part 2" which gives the desired information by
summarizing the above results:

SELECT Z.GameDate, Z.PlayerID, Z.Winner, Count(*)+2 AS GamesInStreak, Min(Z.A.
GameID) AS StreakStart, Max(Z.C.GameID) AS StreakEnd
FROM [qryWin Lose Streaks-Part 1] AS Z
GROUP BY Z.GameDate, Z.PlayerID, Z.Winner;

* * *It might be possible to condense that all into one query. *I leave that
to you to attempt if you wish.

* * * * * * * * * Clifford Bass

pat67 wrote:
I have a table that has results on it with a winner and a loser. what
I am trying to do is get a winning or losing streak query based on
date and game id. meaning a player can play 4 times in one nite so i
would need to first look to date then to game id. so if he one the
first 3 and lost the last one he would have a winning streak of 3. I
hope i am explaining enough. Any ideas? Thanks

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

Since I am using a union query I adjusted your sql. but it still isn't
working. GameDate, GameID, Player, Result (won or lost) are my
fields. here is the query

SELECT A.GameDate, A.Player, A.Result, A.GameID, B.GameID, C.GameID
FROM (qryUnion AS A INNER JOIN qryUnion AS B ON (A.Result = B.
Result) AND (A.Player = B.Player) AND (A.GameDate = B.GameDate))
INNER
JOIN qryUnion AS C ON (B.Result = C.Result) AND (B.Player = C.
Player) AND (B.GameDate = C.GameDate)
WHERE (((B.GameID)=(select Min(GameID) from qryUnion as D where D.
GameDate = A.GameDate and D.Player = A.Player and D.GameID
A.GameID))
AND ((C.GameID)=(select Min(GameID) from qryUnion as E where E.
GameDate = B.GameDate and E.Player = B.Player and E.GameID
B.GameID)));

the error says invalid use of '.', '!', or '()' in query expression
'A.Result=B.Resul'.