Hi,

So here is another way to try:

qryGame Results

SELECT GameID, [Date] AS GameDate, Winner AS Player, True AS IsWinner
FROM tblResults
UNION ALL SELECT GameID, [Date], Loser, False
FROM tblResults;


qryLongest Streaks-Part A

SELECT A.GameDate, A.Player, A.GameID AS StartGameID, B.GameID AS EndGameID,
A.IsWinner, (select count(*) from [qryGame Results] as C where C.GameDate = A.
GameDate and C.Player = A.Player and C.GameID between A.GameID and B.GameID)
AS WinLoseCount
FROM [qryGame Results] AS A INNER JOIN [qryGame Results] AS B ON (A.IsWinner
= B.IsWinner) AND (A.Player = B.Player) AND (A.GameDate = B.GameDate)
WHERE (((B.GameID)=[A].[GameID]) AND ((Not Exists (select * from [qryGame
Results] as D where D.GameDate = A.GameDate and D.Player = A.Player and D.
GameID between A.GameID and B.GameID and D.IsWinner A.IsWinner))=True))
ORDER BY A.GameDate, A.Player, A.GameID, B.GameID;


qryLongest Streaks-Part B

SELECT E.GameDate, E.Player, Max(E.WinLoseCount) AS LongestStreakLength
FROM [qryLongest Streaks-Part A] AS E
GROUP BY E.GameDate, E.Player;

qryLongest Streaks-Part C

SELECT F.GameDate, F.Player, G.LongestStreakLength AS StreakLength, F.
StartGameID, F.EndGameID, IIf([IsWinner],"Winning","Losing") AS StreakType
FROM [qryLongest Streaks-Part A] AS F INNER JOIN [qryLongest Streaks-Part B]
AS G ON (F.GameDate = G.GameDate) AND (F.Player = G.Player) AND (F.
WinLoseCount = G.LongestStreakLength)
ORDER BY F.GameDate, F.Player, F.StartGameID;

This will report the length of the longest streak for each person on a
particular day. It will display the start and end GameIDs of the streak and
whether or not the streak was a winning or losing streak. If the person had
several streaks of that longest streak length, all of those streaks will show.


Clifford Bass

--
Message posted via http://www.accessmonster.com