On Jan 6, 12:07*pm, pat67 wrote:
On Jan 6, 11:58*am, pat67 wrote:





On Jan 6, 11:45*am, "vanderghast" vanderghast@com wrote:

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