counting specific ecords

[Jeff]

Ok, first off, thanks to Bob for all the previous help. I got things running
smooth now.

My next dilemma isn't from that tour that I inherited, but on my own golf
server.

On this, only the loser reports the match. So here are my fields.

username iloser idate

the username is the winner of the match, the iloser is the loser of the
match and idate is of course the date and time the match was played.
What I want to do, is figure out a winning and losing streak. SO it would
need to count to see the most current streak. So I think it would take idae
= Now() and look backwards to see how many times the person's name shows up
in the username field until it sees the same name in the iloser field. this
would say how many wins the person has for that streak. Does this make
sense?
Bam

 

[Jeff]

Was actually hoping I could do this in Access, instead of doing it server
side. Here is my SQL that I have so far.

SELECT rounds.UserName, Sum(quniroundWithMatches.Win) AS Wins,
Sum(Abs([Win]=0)) AS Losses, Sum(1) AS Total_Games, Sum([Win])/Count(*)*100
AS WinPct, Format(WinPct,"0.00") & "%"
FROM quniroundWithMatches INNER JOIN rounds ON
quniroundWithMatches.UserName=rounds.UserName
GROUP BY rounds.UserName;


Is there someway that I can put the streaks in here??

 

[Jeff]

I am thinking about this. It seems like i need to get the most currect
match, and count back records until the name appears in the iloser column,
and get that total. But I just am not sure how to do that.

 

[Jeff]

Nevermind all. I figured out an easy way to do it.

Is there a way to delete an initial post from the NG, like in this case,
where help is no longer needed for this topic??

 

[Bob Barrows [MVP]]

Nevermind all. I figured out an easy way to do it.

Is there a way to delete an initial post from the NG, like in this
case, where help is no longer needed for this topic??

No, but you can help out other users who are googling for help with their
own problems by posting your solution.

Part of the value of using newsgroups, at least in my experience, is the
opportunity to help others as you receive help.

 

[Jeff]

Sure thing Bob.

This is what I did.

I created fields in the members table called winSTR loseSTR winSTRbest
loseSTRbest
The winSTR is the current streak, loseSTR is the current losing streak,
winSTRbest is the best winning streak the player has had, and loseSTRbest is
the worse losing streak the player has had.

Now on the reportasp.asp page, since only the loser reports, I put in a few
sql statements.

The first thing it did was set the reporting person's currnet win streak to
0, since he just lost and that is why he is reporting.
Then the next statement added a win the the person who won's current win
streak field.
Then I had it do a check, to see if the current win streak, was better than
the best win streak, if so replace it, if not, continue on.
Then it added one to the losers current losing streak, and checked to see if
that was higher than the worst losing streak, if so, replace it, if not,
continue on, which is closing the connection, and a response.redirect

Not sure if this was the most efficient way of doing it, but it would seem
it would be faster than going through the table of matches (which is about
8000 records). It takes less than a second to process this, so it works fine
for me.
Hope this will help someone
Bam