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
  #1  
Old January 4th, 2010, 10:08 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 5th, 2010, 01:28 AM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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  
Old January 5th, 2010, 06:38 PM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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  
Old January 5th, 2010, 07:20 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old January 5th, 2010, 10:09 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 5th, 2010, 10:27 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 5th, 2010, 10:27 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old January 5th, 2010, 10:39 PM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default 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  
Old January 5th, 2010, 10:53 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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  
Old January 5th, 2010, 10:59 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default 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

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 07:46 PM.


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