If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
winning streak query
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 |
#3
|
|||
|
|||
winning streak query
Hi,
Interesting solution. I will have to look at it some more to totally understand it. Under the presumption from pat67's examples that a steak is three or more wins/loses I think you will want to add "HAVING MAX(streak) = 3" to the final query. Your data set would need to be expanded or adjusted if there are multiple, simultaneous games happening. Also, I think pat67 wanted both winning and losing streaks. And I think the streaks were streaks on a particular day, not over multiple days. pat67: correct me if I am wrong there. Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 |
#4
|
|||
|
|||
winning streak query
Indeed, you need to add the proposed HAVING clause in that case
To get only one day, replace myData (deep most query) by a query that will limit the data to that one day, something with WHERE DateValue(gameDateTimeStamp) = someDate. To get losing streaks, replace isWin by NOT isWin (2 places in the deep most query). As long as the couple (playerID, gameDateTimeStamp) has no dup, the logic stands, even if there are many games with the same datetime stamp. It won't if the same player can be in many simultaneous games, like a chess master player playing simultaneous chess games. Vanderghast, Access MVP "Clifford Bass via AccessMonster.com" u48370@uwe wrote in message news:a1aa817b9720f@uwe... Hi, Interesting solution. I will have to look at it some more to totally understand it. Under the presumption from pat67's examples that a steak is three or more wins/loses I think you will want to add "HAVING MAX(streak) = 3" to the final query. Your data set would need to be expanded or adjusted if there are multiple, simultaneous games happening. Also, I think pat67 wanted both winning and losing streaks. And I think the streaks were streaks on a particular day, not over multiple days. pat67: correct me if I am wrong there. Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 |
#5
|
|||
|
|||
winning streak query
On Jan 5, 1:20*pm, "vanderghast" vanderghast@com wrote:
Indeed, you need to add the proposed HAVING clause in that case To get only one day, replace myData (deep most query) by a query that will limit the data to that one day, something with WHERE DateValue(gameDateTimeStamp) = someDate. To get losing streaks, replace isWin by NOT isWin *(2 places in the deep most query). As long as the couple (playerID, gameDateTimeStamp) *has no dup, the logic stands, even if there are many games with the same datetime stamp. It won't if the same player can be in many simultaneous games, like a chess master player playing simultaneous chess games. Vanderghast, Access MVP "Clifford Bass via AccessMonster.com" u48370@uwe wrote in messagenews:a1aa817b9720f@uwe... Hi, * * Interesting solution. *I will have to look at it some more to totally understand it. * * Under the presumption from pat67's examples that a steak is three or more wins/loses I think you will want to add "HAVING MAX(streak) = 3" to the final query. *Your data set would need to be expanded or adjusted if there are multiple, simultaneous games happening. *Also, I think pat67 wanted both winning and losing streaks. *And I think the streaks were streaks on a particular day, not over multiple days. * * pat67: correct me if I am wrong there. * * * * * *Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ries/201001/1- Hide quoted text - - Show quoted text - Wow. let me clarify more if I can. I just used 3 as an example. If a players longest winning streak is 1 or 100, that's what I want to show. My data table has these fields: GameID Date Winner Loser I then have a union query with these fields: Date GameID Player Opponent Result result being Won or Lost So that means that there are 2 results for each game id. does that make it easier? |
#6
|
|||
|
|||
winning streak query
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'. |
#7
|
|||
|
|||
winning streak query
Wow. let me clarify more if I can. I just used 3 as an example. If a players longest winning streak is 1 or 100, that's what I want to show. Then you use the unmodified query (ie, without any HAVING clause; the use of HAVING was only a remark from Clifford) My data table has these fields: GameID Date Winner Loser I then have a union query with these fields: Date GameID Player Opponent Result result being Won or Lost So that means that there are 2 results for each game id. does that make it easier? What you need is (at least) DateOfTheGame, PlayerID, IsPlayerWinOrLost which probably could be obtained from a query like: SELECT [date] as gameDateTimeStamp, Winner As PlayerID, true AS isWin FROM dataTable UNION ALL SELECT [date], Loser, false FROM dataTable assuming that, from your original data table, Winner and Loser return a PlayerID (who is the winner and who is the loser). Saving that query as myData and using the proposed query should return what you want (the highest winning-streak for each player). Vanderghast, Access MVP |
#8
|
|||
|
|||
winning streak query
Hi,
Just to be su Is that per day? Or across all days? Do you want all of the streaks? Or just the longest? Is that just the longest of either winning or losing? Or the longest of both? Is a single win or loss really a streak? That seems to be contradictory. Can a player play more than one game at once? Do later games always have higher GameIDs then earlier games? Other factors of importance? Clifford Bass pat67 wrote: Wow. let me clarify more if I can. I just used 3 as an example. If a players longest winning streak is 1 or 100, that's what I want to show. My data table has these fields: GameID Date Winner Loser I then have a union query with these fields: Date GameID Player Opponent Result result being Won or Lost So that means that there are 2 results for each game id. does that make it easier? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 |
#9
|
|||
|
|||
winning streak query
On Jan 5, 4:39*pm, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote: Hi, * * *Just to be su Is that per day? *Or across all days? *Do you want all of the streaks? *Or just the longest? *Is that just the longest of either winning or losing? *Or the longest of both? *Is a single win or loss really a streak? *That seems to be contradictory. *Can a player play more than one game at once? *Do later games always have higher GameIDs then earlier games? Other factors of importance? * * * * * * Clifford Bass pat67 wrote: Wow. let me clarify more if I can. I just used 3 as an example. If a players longest winning streak is 1 or 100, that's what I want to show. My data table has these fields: GameID * *Date * *Winner * *Loser I then have a union query with these fields: Date GameID Player * Opponent *Result result being Won or Lost So that means that there are 2 results for each game id. does that make it easier? -- Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1- Hide quoted text - - Show quoted text - longest streak. GameID is from an autonumber field in my table so it is always increasing. 1 is a winning streak, yes technically. |
#10
|
|||
|
|||
winning streak query
On Jan 5, 4:27*pm, "vanderghast" vanderghast@com wrote:
Wow. let me clarify more if I can. I just used 3 as an example. If a players longest winning streak is 1 or 100, that's what I want to show. Then you use the unmodified query (ie, without any HAVING clause; the use of HAVING was only a remark from Clifford) My data table has these fields: GameID * *Date * *Winner * *Loser I then have a union query with these fields: Date GameID Player * Opponent *Result result being Won or Lost So that means that there are 2 results for each game id. does that make it easier? What you need is (at least) DateOfTheGame, PlayerID, IsPlayerWinOrLost which probably could be obtained from a query like: SELECT [date] as gameDateTimeStamp, Winner As PlayerID, true AS isWin FROM dataTable UNION ALL SELECT [date], Loser, false FROM dataTable assuming that, from your original data table, Winner and Loser return a PlayerID (who is the winner and who is the loser). Saving that query as myData and using the proposed query should return what you want (the highest winning-streak for each player). Vanderghast, Access MVP Ok. that gives a result of -1 for the winners and 0 for the losers. Is that correct? Then i use your queries you stated originally? |
Thread Tools | |
Display Modes | |
|
|