winning streak query

[pat67]

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

 

[vanderghast]

If the game are associated to a date_time stamp, then you can rank, by
player, over the date_time, the two sequences: rank all games, rank only
winning games (again, by player). You can then compute Max(rank all
games) - Min(rank all games) +1, over the GROUP BY player, rank_all_games -
rank_only_winning_games, to get the various streak.

Than can be (easily) done in multiple queries:

Final query:
-------------
SELECT playerID, MAX(streak)
FROM qStreaks
GROUP BY playerID
------------------

returns the maximum number of consecutive wins, by players.


Query: qStreaks:
------------------
SELECT playerID, 1+MAX(rank_all) - MIN(rank_all) AS streak
FROM qRanks
GROUP BY playerID, rank_all - rank_onlyWins
----------------

which returns the various streaks, by player.

Query: qRanks:
-----------------------
SELECT a.playerID,
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
GROUP BY a.playerID, a.gameDateTimeStamp
--------------------------

which ranks each (wnning) game, by player, over all the game and also over
only the winning games.

Note that I assumed the original table has at least the fields: playerID,
gameDateTimeStamp, and isWin (Boolean, = true for a win, = false for a
lost).

The idea to substract two ranks may seems 'weird', but consider that the
player won all his games: the difference between the two ranks will always
be 0. On the other hand, if the player lost one game in the middle of the
sequence, then the difference (for the two ranks) for the first games will
be 0 as before, but for the later games, the difference will be 1 (since the
rank_all will see a game that rank_onlyWins won't see) and thus, you have to
count the number of records where the difference is 0 to get the first
streak of wins, and count the number of records where the difference is 1 to
get the last streak of wins. Exactly what qStreaks does, for a general
sequence of wins/lost sequences of games. (instead of 'counting' the
records, which will require a sub-query, we take an alternative route,
playing with MIN and MAX, but that elaborate formulation implyinng Max and
Min just does our 'count', in the end).

The last query, qRanks, compute the two required ranks using sub-query. it
can be quite slow if you have a large amount of data. If you use MS SQL
Server 2005 or later, you could preferably use the pre-defined rank operator
(Jet does not have such operator).



Vanderghast, Access MVP

 

[vanderghast]

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

 

[pat67]

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









- 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?

 

[pat67]

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.  Ileave that
to you to attempt if you wish.

                  Clifford Bass

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

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'.

 

[vanderghast]

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

 

[pat67]

Hi,

     Just to be sure: Is that per day?  Or across all days?  Doyou 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 thanone
game at once?  Do later games always have higher GameIDs then earlier games?
Other factors of importance?

            Clifford Bass

longest streak. GameID is from an autonumber field in my table so it
is always increasing. 1 is a winning streak, yes technically.

 

[pat67]

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 useof
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?

 

[vanderghast]

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

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?

 

[pat67]

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

Then you use the unmodified query (ie, without any HAVING clause; the use
of
HAVING was only a remark from Clifford)

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 quotedtext -

- Show quoted text -

That looks like it worked. It takes a while to run, but it looks right

 

[pat67]

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" <[email protected]> wrote in message

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; theuse
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 returna
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?

 

[pat67]

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
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 quotedtext -

- 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?

 

[pat67]

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
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;

 

[vanderghast]

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 anymore: 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

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
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?

 

[pat67]

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 anymore: John played at 10AM andat
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

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
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?

 

[pat67]

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 anymore: 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. Sothe
importance to have a time part.

And yes, it takes time, unfortunately Jet has not implemented, yet, anyRANK
operator.

Vanderghast, Access MVP

"pat67" <[email protected]> wrote in message

Yes. With Jet, a Boolean False is 0 and True is -1. In fact, Trueis
anything thing not null neither 0, but the result of a comparison
is -1:
? int(3=3)
-1
Vanderghast, Access MVP
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?

 

[pat67]

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 anymore: John played at 10AMand 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
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
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 ina
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.

 

[vanderghast]

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

 

[vanderghast]

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

 

[pat67]

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?