Comparing Dates in rows/records

[Keenan]

Hi

Wondering how I can work out the number of days between games for a sport
fixture with 16 teams, 20 rounds. example below:

Table example:
G - Rnd - Game Date - Team
1 - 1 - 1-Jan-09 - Black
2 - 1 - 2-Jan-09 - Yellow
3 - 2 - 8-Jan-09 - Yellow
4 - 2 - 9-Jan-09 - Black
etc, etc, etc

Query example: to show all games for team Black:
Rnd - Game Date - Team - days between last game
1 - 1-Jan-09 - Black - 0
2 - 9-Jan-09 - Black - 8
3 - 15-Jan-09 - Black - 6
4 - 22-Jan-09 - Black - 7
etc, etc

Hope I have given you enough information. If I should set up the
tables/queries differently let me know.

 

[Allen Browne]

Use a subquery to match the most recent previous date for the same team.

Details and:
http://allenbrowne.com/subquery-01.html#AnotherRecord
The example is for a meter reading, but you can use it the same way for
sports games.

Once you have the previous date, you can use DateDiff() to calculate the
number of days.