Find consecutive records

[millerlite2ky]

I have a database consisting of race results. For the recordbook, without having to manually do this, I'd like to find out which driver has the record for most consecutive starts, wins, etc. Is there a way to do this for a report or a form.

Scott

 

[Andrew Fergus]

Hi Scott

In answer to your question :

When I did, the Query1 in design window changed to Query1Alias. Is this right?

Yes - even though it is an "alias" it changes the name of the query / table in the upper half of the query window. The "alias" is necessary for the ranking query. And for this question :

Also, are you talking about deleting the existing relationship between my Drivers table and Events table in the Relationship window?

No - when I say "delete the link" I mean in the query screen, not the relationship screen - you have to delete the link in the query (not the relationship) otherwise the consecutive queries won't work. The reason we delete the link is to get an event for every dirver, whether they started or not, in order to determine consecutivity (is there such a word?).

Also, once you get this working and you have your answer(s), you might want to exclude some records from the queries to speed them up given that retired drivers will no longer top the list of most consecutive starts / wins etc (unless they are already number 1).

HTH, Andrew.