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?