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
  #11  
Old January 6th, 2010, 02:53 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default winning streak query

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?

  #12  
Old January 6th, 2010, 04:04 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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
  #13  
Old January 6th, 2010, 05:09 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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?
  #14  
Old January 6th, 2010, 05:17 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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?
  #15  
Old January 6th, 2010, 05:33 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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;


  #16  
Old January 6th, 2010, 05:45 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default winning streak query

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?

  #17  
Old January 6th, 2010, 05:58 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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?
  #18  
Old January 6th, 2010, 06:07 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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?
  #19  
Old January 6th, 2010, 06:21 PM posted to microsoft.public.access.queries
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default winning streak query

Hi,

I will putter around with this when I get a chance. Unless someone else
gets you to a functioning solution before I get the chance.

Clifford Bass

pat67 wrote:
On Jan 5, 4:39Â*pm, "Clifford Bass via AccessMonster.com" u48370@uwe
wrote:

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201001/1

  #20  
Old January 6th, 2010, 06:28 PM posted to microsoft.public.access.queries
pat67
external usenet poster
 
Posts: 137
Default winning streak query

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.
 




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 04:20 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.