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 |
#21
|
|||
|
|||
winning streak query
Note that if GameID is already unique, you can use it instead of [Date].
Indeed, if GameID is unique, then (PlayerID, GameID) will also be unique (unless a player plays against himself!). You will have to change the SQL statements to replace [Date] by GameID. Vanderghast, Access MVP |
#22
|
|||
|
|||
winning streak query
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 |
#23
|
|||
|
|||
winning streak query
On Jan 6, 12:56*pm, "vanderghast" vanderghast@com wrote:
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? |
#24
|
|||
|
|||
winning streak query
On Jan 6, 1:23*pm, pat67 wrote:
On Jan 6, 12:56*pm, "vanderghast" vanderghast@com wrote: 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? 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. |
#25
|
|||
|
|||
winning streak query
You were probably using string rather than date_time as DATA TYPE for that
field (check the table design). Vanderghast, Access MVP "pat67" wrote in message ... On Jan 6, 12:56 pm, "vanderghast" vanderghast@com wrote: 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? |
#26
|
|||
|
|||
winning streak query
On Jan 6, 12:56*pm, "vanderghast" vanderghast@com wrote:
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 |
#27
|
|||
|
|||
winning streak query
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 |
#28
|
|||
|
|||
winning streak query
On Jan 7, 10:25*am, "vanderghast" vanderghast@com wrote:
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 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? |
#29
|
|||
|
|||
winning streak query
On Jan 7, 10:25*am, "vanderghast" vanderghast@com wrote:
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 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 |
#30
|
|||
|
|||
winning streak query
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 |
Thread Tools | |
Display Modes | |
|
|