Winning or Losing Streak 1

[KavJack]

Having just had a successfull response to my first question, I'll have a go with this one. I think that this is a bit harder.
I have a database of Football Results. The Table looks like this:
Field Name Data Type
SEASON Number
HomeTeam Text
HomeScore Number
AwayTeam Text
AwayScore Number
DATE Number - as YYYYMMDD

The question is for a particular team I want to find their longest winning or losing streak. So obviously the records have to be sorted in date order. Each record represents one game. We don't know how long the streak is going to be ?
Also the HomeTeam for one game could be the AwayTeam for the next game or it could be the HomeTeam again.
The result to be returned should be at least one of the following; the number representing the number of games in the streak, the date of the start or end of the streak,
and possibly the games (records) making up the streak.
A winning streak would be terminated by a non-win, or a losing streak by a non-loss. HomeScore could equal AwayScore so that would be neither a win nor a loss for either team. I have tried to do this using VBA but I couldn't save the interim values. My database has 161,000 records (one for each game) and there are approx. 128 team names so it's a fairly large database.

 

[MichaelRed]

Hmmmmmmmmmmmm,

If I had some sample data, I believe it would be easy.

Construct a new temp recordset w/ 2 records for each rec in your table.

tblTeamGame:
GDate[tab]Date of Game
Team[tab]Name of A team in the Rec
TScore[tab]That Team's Score
OScore[tab]The other team's Score


You COULD - in generating the secondary recordset, calculate the Win / Loss for each game as a Calculated Field.

Fairly simple queries would provide the teams ordered by their Win / Loss Streaks.


Or,
Sort by the Team & GDate
Do your loop.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over