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. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
winning streak query
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 |
Thread Tools | |
Display Modes | |
|
|