Thread: Count records between two results

Hello,

I have a table that stores football results that looks thus

FREF  Fdate      SREF   HomeTeam AwayTeam HomeScore AwayScore Result Venue Attend Ref     Report CompREF MREF    BGcolor Fcolor Done
20001 22/09/2010 200910 ExeC     PlyA     3         0         W      SJP   5000   BloggsJ Text   CCL1    BloggsT red     blue   Y

etc

I'd like to be able to count what the highest number of consecutive wins is (Result="W") when ordered by FDate

Any ideas?

Hi Alan, I don't think i've explained myself properly, i'll have another go.

Consider a table, Fixtures

FDate       Result
01/01/2000  W
02/01/2000  D
03/01/2000  D
04/01/2000  D
05/01/2000  W
06/01/2000  W
07/01/2000  L 
08/01/2000  W

I'd like a query that will count the number of consecutive duplicates when ordered by FDate. Therefore giving the result

Result  Count  FDate
D       3      02/01/2000
W       2      05/01/2000
W       1      01/01/2000
L       1      07/01/2000
W       1      08/01/2000

Hope this makes sense