On Jan 6, 11:17*am, pat67 wrote:
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 -

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;