Finding Trends

[J Dizzle Fizzle]

I have table which has the results of various contests:

Year Team Result
2007 A W
2003 A L
2004 A L
2005 A W
2006 A W
2007 A W

I would like to be able to query specific trends, like current streak. In
this case would be 3 W and longest streak of each type, 2 L and 3 W in this
example.

Is there an easy way to do this?

Thanks in advance!

 

[Michel Walsh]

I assume the first line is about 2002, not 2007.

Make a first query to rank:


SELECT a.year, a.team, a.result, COUNT(*) AS rank
FROM myTable As a INNER JOIN myTable AS b
ON a.team=b.team
AND (a.result > b.result
OR (a.result = b.result
AND a.year >= b.year))
GROUP BY a.year, a.team, a.result

Save it under the name, say, qrank.

That should give:

Year Team Result Rank
2002 A W 3
2003 A L 1
2004 A L 2
2005 A W 4
2006 A W 5
2007 A W 6


The result can be obtained from:


SELECT team, LAST(result) AS theResult, MIN(year), MAX(year), COUNT(*) AS
streak
FROM qrank
GROUP BY team, year-rank



Indeed, that should give

(intermediate result) :
Year Team Result Rank Year-Rank
2002 A W 3 1999
2003 A L 1 2002
2004 A L 2 2002
2005 A W 4 2001
2006 A W 5 2001
2007 A W 6 2001


so, making the groups:


team year-rank COUNT(*) LAST(result) MIN(year)
A 1999 1 W 2002
A 2002 2 L 2003
A 2001 3 W 2005



Note that the computed column year-rank is just used to make a group and has
no useful meaning, so it is not 'select'-ed for the result.


If you want the maximum streak, save the previous query, streaks, and use a
query like:

SELECT team, theResult, MAX(streak)
FROM streaks
GROUP BY team, theResult




Hoping it may help,
Vanderghast, Access MVP