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 |
#11
|
|||
|
|||
winning streak query
Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is
anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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? |
#12
|
|||
|
|||
winning streak query
On Jan 6, 8:53*am, "vanderghast" vanderghast@com wrote:
Yes. With Jet, a Boolean False is 0 and True is -1. *In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right |
#13
|
|||
|
|||
winning streak query
On Jan 6, 10:04*am, pat67 wrote:
On Jan 6, 8:53*am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. *In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message .... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1? |
#14
|
|||
|
|||
winning streak query
On Jan 6, 11:09*am, pat67 wrote:
On Jan 6, 10:04*am, pat67 wrote: On Jan 6, 8:53*am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. *In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message .... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1?- Hide quoted text - - Show quoted text - I checked the top rated guy who shows 12, which he is currently on. In reality it should be 15? what could be the problem? |
#15
|
|||
|
|||
winning streak query
On Jan 6, 11:17*am, pat67 wrote:
On Jan 6, 11:09*am, pat67 wrote: On Jan 6, 10:04*am, pat67 wrote: On Jan 6, 8:53*am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. *In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1?- Hide quoted text - - Show quoted text - I checked the top rated guy who shows 12, which he is currently on. In reality it should be 15? what could be the problem?- Hide quoted text - - Show quoted text - I think the problem is that players play multiple games on one night and by just using the date and not a game id the software doesn't know how many where in a row. i.e. Player A was 2 wins and 2 losses on 12/15. Without using game id the software would know he won 2 then lost 2. it puts the streak at 1. So i added game id to the first 2 queries but the last 2 I am not sure about. here is what the first 2 look like now myData SELECT ID as GameID, [Date] as GameDateTimeStamp, Winner As PlayerID, true AS isWin FROM tblResults UNION ALL SELECT ID, [Date], Loser, false FROM tblResults; qRanks SELECT a.playerID, a.GameID, a.gameDateTimeStamp, (SELECT COUNT(*) FROM myData AS b WHERE b.playerID=a.playerID AND b.gameDateTimeStamp = a.gameDateTimeStamp) AS rank_all, (SELECT COUNT(*) FROM myData AS c WHERE c.playerID=a.playerID AND c.isWin AND c.gameDateTimeStamp = a.gameDateTimeStamp) AS rank_onlyWins FROM myData AS a WHERE (((a.isWin)False)) GROUP BY a.playerID, a.GameID, a.gameDateTimeStamp; |
#16
|
|||
|
|||
winning streak query
If a player plays twice the same day, the [date] field must have a TIME
value (other than 0), and a DIFFERENT one for each 'game' : Player date isWin john 2010.1.1 -1 tom 2010.1.1 0 john 2010.1.1 -1 mary 2010.1.1 0 is wrong for John, since (Player, date) is duplicated (first and third record). The following would be nice, though: Player date isWin john 2010.1.1 10:00:00 -1 tom 2010.1.1 10:00:00 0 john 2010.1.1 11:00:00 -1 mary 2010.1.1 11:00:00 0 since then, (Player, date) has no dup anymo John played at 10AM and at 11AM, that makes 2 different games. Without the time part, the presented algorithm will see only one game for John, for the first of January. So the importance to have a time part. And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK operator. Vanderghast, Access MVP "pat67" wrote in message ... On Jan 6, 11:09 am, pat67 wrote: On Jan 6, 10:04 am, pat67 wrote: On Jan 6, 8:53 am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1?- Hide quoted text - - Show quoted text - I checked the top rated guy who shows 12, which he is currently on. In reality it should be 15? what could be the problem? |
#17
|
|||
|
|||
winning streak query
On Jan 6, 11:45*am, "vanderghast" vanderghast@com wrote:
If a player plays twice the same day, the [date] field must have a TIME value (other than 0), and a DIFFERENT one for each 'game' : Player * * *date * * * * * * * *isWin john * * * *2010.1.1 * * * -1 tom * * * * 2010.1.1 * * * *0 john * * * *2010.1.1 * * * *-1 mary * * * *2010.1.1 * * * 0 is wrong for John, since (Player, date) *is duplicated (first and third record). *The following would be nice, though: Player * * *date * * * * * * * * * * * * *isWin john * * * *2010.1.1 10:00:00 * * * -1 tom * * * * 2010.1.1 *10:00:00 * * * *0 john * * * *2010.1.1 * 11:00:00 * * -1 mary * * * *2010.1.1 * 11:00:00 * * 0 since then, (Player, date) *has no dup anymo John played at 10AM and at 11AM, that makes 2 different games. Without the time part, the presented algorithm will see only one game for John, for the first of January. So the importance to have a time part. And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK operator. Vanderghast, Access MVP "pat67" wrote in message ... On Jan 6, 11:09 am, pat67 wrote: On Jan 6, 10:04 am, pat67 wrote: On Jan 6, 8:53 am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1?- Hide quoted text - - Show quoted text - I checked the top rated guy who shows 12, which he is currently on. In reality it should be 15? what could be the problem?- Hide quoted text - - Show quoted text - can I use game id as opposed to time? |
#18
|
|||
|
|||
winning streak query
On Jan 6, 11:58*am, pat67 wrote:
On Jan 6, 11:45*am, "vanderghast" vanderghast@com wrote: If a player plays twice the same day, the [date] field must have a TIME value (other than 0), and a DIFFERENT one for each 'game' : Player * * *date * * * * * * * *isWin john * * * *2010.1.1 * * * -1 tom * * * * 2010.1.1 * * * *0 john * * * *2010.1.1 * * * *-1 mary * * * *2010.1.1 * * * 0 is wrong for John, since (Player, date) *is duplicated (first and third record). *The following would be nice, though: Player * * *date * * * * * * * * * * * * *isWin john * * * *2010.1.1 10:00:00 * * * -1 tom * * * * 2010.1.1 *10:00:00 * * * *0 john * * * *2010.1.1 * 11:00:00 * * -1 mary * * * *2010.1.1 * 11:00:00 * * 0 since then, (Player, date) *has no dup anymo John played at 10AM and at 11AM, that makes 2 different games. Without the time part, the presented algorithm will see only one game for John, for the first of January. So the importance to have a time part. And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK operator. Vanderghast, Access MVP "pat67" wrote in message ... On Jan 6, 11:09 am, pat67 wrote: On Jan 6, 10:04 am, pat67 wrote: On Jan 6, 8:53 am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1?- Hide quoted text - - Show quoted text - I checked the top rated guy who shows 12, which he is currently on. In reality it should be 15? what could be the problem?- Hide quoted text - - Show quoted text - can I use game id as opposed to time?- Hide quoted text - - Show quoted text - I mean can i concatenate the date and id to look like this 9/15/2009-1? |
#19
|
|||
|
|||
winning streak query
Hi,
I will putter around with this when I get a chance. Unless someone else gets you to a functioning solution before I get the chance. Clifford Bass pat67 wrote: On Jan 5, 4:39Â*pm, "Clifford Bass via AccessMonster.com" u48370@uwe wrote: longest streak. GameID is from an autonumber field in my table so it is always increasing. 1 is a winning streak, yes technically. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201001/1 |
#20
|
|||
|
|||
winning streak query
On Jan 6, 12:07*pm, pat67 wrote:
On Jan 6, 11:58*am, pat67 wrote: On Jan 6, 11:45*am, "vanderghast" vanderghast@com wrote: If a player plays twice the same day, the [date] field must have a TIME value (other than 0), and a DIFFERENT one for each 'game' : Player * * *date * * * * * * * *isWin john * * * *2010.1.1 * * * -1 tom * * * * 2010.1.1 * * * *0 john * * * *2010.1.1 * * * *-1 mary * * * *2010.1.1 * * * 0 is wrong for John, since (Player, date) *is duplicated (first and third record). *The following would be nice, though: Player * * *date * * * * * * * * * * * * *isWin john * * * *2010.1.1 10:00:00 * * * -1 tom * * * * 2010.1.1 *10:00:00 * * * *0 john * * * *2010.1.1 * 11:00:00 * * -1 mary * * * *2010.1.1 * 11:00:00 * * 0 since then, (Player, date) *has no dup anymo John played at 10AM and at 11AM, that makes 2 different games. Without the time part, the presented algorithm will see only one game for John, for the first of January. So the importance to have a time part. And yes, it takes time, unfortunately Jet has not implemented, yet, any RANK operator. Vanderghast, Access MVP "pat67" wrote in message .... On Jan 6, 11:09 am, pat67 wrote: On Jan 6, 10:04 am, pat67 wrote: On Jan 6, 8:53 am, "vanderghast" vanderghast@com wrote: Yes. With Jet, a Boolean False is 0 and True is -1. In fact, True is anything thing not null neither 0, but the result of a comparison is -1: ? int(3=3) -1 Vanderghast, Access MVP "pat67" wrote in message ... 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?- Hide quoted text - - Show quoted text - That looks like it worked. It takes a while to run, but it looks right- Hide quoted text - - Show quoted text - Ok there is a problem. The queries work however i see at least one player not right. The first night of the year he lost then won 2 in a row and lost again so his win streak should at least be 2. it is showing as 1. Is that because the first query returned a -1?- Hide quoted text - - Show quoted text - I checked the top rated guy who shows 12, which he is currently on. In reality it should be 15? what could be the problem?- Hide quoted text - - Show quoted text - can I use game id as opposed to time?- Hide quoted text - - Show quoted text - I mean can i concatenate the date and id to look like this 9/15/2009-1?- Hide quoted text - - Show quoted text - ok i concatenated the date and game id. that made some changes but still not correct. sql now like this myData SELECT [Date] & '-' & ID as GameDateTimeStamp, Winner As PlayerID, true AS isWin FROM tblResults UNION ALL SELECT [Date] & '-' & ID, Loser, false FROM tblResults; problem is now top player says 13. but should be 15. I think i need to change the date format to be 09/15/2009. with just 9/15/2009, any 12 or 11 or 10 is sorted before. |
Thread Tools | |
Display Modes | |
|
|