Hi Scott
Apologies in advance for the long answer but this was a lot harder than I had imagined it would be. I've managed to get it to work with very similar tables using test data - but to get it to work I had to use an autonumber for the driver id.
A couple of questions : is your driver id an autonumber? Does the event table include the event date? ( <- Silly question I know but I had to ask it...) Also, is the driver's start in a race recorded by way of being present in the results table? Or something else? ....
Event table is set up with about 30 columns/fields of data including Driver ID.
The bit above has me confused - is the driver id recorded in the event table? I wouldn't have thought that was necessary given the drivers and events are "brought together" in the results table.
If a driver starts a race but DNF's - how is this recorded? I used yes/no variables in the results table to indicate if a driver started and / or finished a race, and another variable to record the drivers final position.
Anyway, questions aside, the way I got it to work was to create a select query based on the driver table and event table (with no links whatsoever), added the driver id, name, event id, description, date and a "sequence" field (sort ascending), as follows :
Sequence: ( [Driver_ID] * 1000000) + Format( Year( [Event_Date]), "0000" ) & Format( Month( [Event_Date] ),"00" ) & Format( Day( [Event_Date] ),"00" )
Save the query.
This "sequence" is used to give you the sort order of the records for determining consecutive starts, wins etc. later on. We need this to create a value that we can use to rank the records that will provide an incremental number - which we need when comparing one record to another in a query to determine whether something is consecutive or not.
This query creates a record for every driver for every race - this is necessary to include the races in which a driver did not start.
Please note that I have assumed there won't be more than 1m events in making a unique value - you can change the 1m bit to more if you want.
I then created another select query, based on the first query included the driver id, name, event id, description, date and a new field called "ranking", see below :
Ranking: (Select Count(*) from Query1 where [Sequence] < [Query1Alias].[Sequence] ; )+1 {I discussed the table alias in a previous post but it is necessary for the "ranking" - use your actual query & variable names, ask if you're not sure}
Save the query.
I then created a 3rd query, based on the 2nd query and the results table, linked on the event id and driver id (go into the link properties and force both links pick up all records from the query and just the matching records from the table), add the ranking (sort ascending), driver id, name, event id, description, date, "start", "finish" and "place" variables (see below - slight name changes from the actual variable names in the results table), a "samedriver" variable (see below), and a "starts" or "wins" variable (see below).
The "start" & "finish" variables came from the results table (I used yes/no indicators in the results table to indicate whether a driver had started and / or finished the race) and the "place" variable also came from the results table (I used a numerical value to indicate the place in the race, with 1 = 1st, 2 = 2nd and so on and 0 = DNF and / or DNS).
In the query, "start", "finish" and "place" were coded like this :
Start: IIf( [Started] = Yes, 1, 0 ) {NB the actual variable name in the results table was "started"}
Finish: IIf( [Finished] = Yes, 1, 0 ) {NB I actually used "finished" in the results table}
Place: IIf( IsNull( [Position] ), 0, [Position] ) {NB I used "position" in the results table}
The details for the "samedriver" is :
SameDriver: IIf([Query2].[Driver_ID] = (DLookUp( "[Driver_ID]", "Query2","Ranking =" & [Ranking]-1)),1,0) {use your real query 2 name}
In the same query, you can calculate the consecutive starts with this :
Consec_Starts: IIf( [Start] =0, 0, IIf( [SameDriver]=0, 1, [Start] + (DLookUp( "[Consec_Starts]", "[Query3]", "Ranking = " & [Ranking]-1)))) {use your real query 3 name}
Or consecutive wins with this :
Consec_Wins: IIf([Start]=0, 0, IIf([SameDriver]=0, IIf([Place]=1, 1, 0 ) , IIf( [Place] <> 1, 0, [Place] + (DLookUp("[Consec_Wins]", "[Query3]", "Ranking = " & [Ranking]-1 ))))) {use your real query 3 name}
Save the query.
...and so on for consecutive finishes, DNF's, 2nds - whatever you want. Please note that the query really slows down with lots of data and the more fields you add the slower it gets, so you might want to do wins in one query and starts in another and so forth. I'm using a P4 and it was noticeably slow with only 20 race result records.
To finish it off, I created a 4th query based on the 3rd query, added the "starts" and "wins" variables, view totals, set both totals to "Max".
Save the query.
Lastly, to retrieve just the "max" records, I created a 5th query based on the 3rd and 4th queries, linked the "wins" in query 3 with "maxofwins" in query 4 (or "starts" & "maxofstarts") and then included all records from query 3.
Save and run and hey presto, your max consecutive wins (or starts) with the driver and event in which it happened.
Someone smarter than I may be able to code all of this in VB to make it quicker and more efficient - as I mentioned earlier my query was very slow even when using a P4 PC.
HTH, Andrew.
