A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

winning streak query



 
 
Thread Tools Display Modes
  #21  
Old January 6th, 2010, 06:49 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old January 6th, 2010, 06:56 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old January 6th, 2010, 07:23 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 6th, 2010, 07:59 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 6th, 2010, 09:45 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old January 7th, 2010, 03:09 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 7th, 2010, 04:25 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old January 7th, 2010, 04:38 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 7th, 2010, 04:48 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 10th, 2010, 11:17 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.