Consecutive events

[Guest]

I have a racing related database, and I am making some specialized reports.
I want to show consecutive wins by a specific driver at a track. I have a
query that returns all the race winners, but I want to group them in a way to
show that Driver Dale Earnhardt Jr had won 4 consecutive races at Talladega.

I have used a cheat work around where I put a field name in the
tblRaceFinish table called ConsecutiveWins. Then, I manually put the number
in the record. So where he won four in a row, in the four records returned,
each field has a four in it, for the four races in a row at the track he won.
However, lets say in the above example Dale Earnhardt Jr. wins another three
straight at Talladega. I would have to go in and manually change the record
from 4 to 5, then 5 to 6, then 6 to 7.

Not exactly how I want to do it.

I think I'm close, but it just isn't coming to me.

Any ideas?

Thanks,

Don

 

[Marshall Barton]

I have a racing related database, and I am making some specialized reports.
I want to show consecutive wins by a specific driver at a track. I have a
query that returns all the race winners, but I want to group them in a way to
show that Driver Dale Earnhardt Jr had won 4 consecutive races at Talladega.

I have used a cheat work around where I put a field name in the
tblRaceFinish table called ConsecutiveWins. Then, I manually put the number
in the record. So where he won four in a row, in the four records returned,
each field has a four in it, for the four races in a row at the track he won.
However, lets say in the above example Dale Earnhardt Jr. wins another three
straight at Talladega. I would have to go in and manually change the record
from 4 to 5, then 5 to 6, then 6 to 7.

How do you define "consecutive"? Does each race have a
sequence number? If so, is it by the year for all races or
at each race site? For that matter, does a site have more
than one race per year??

 

[Guest]

Consecutive races at each track. For example, Talladega

had 2 races in 2004,
one in April, one in October.

I have a 'RaceID' for each 'YearID' and I also have an

overall race number
starting with the first race back in 1949 up throught

present.

What I've done is query the winners, and then trying to

sort or group them
in a way that would show like my previous example, where

Earnhardt Jr. won
the fall race in 2001 at Talladega, both the spring and

fall race in 2002 and
the spring race in 2003. I want to be able to show that

he won 4 consecutive
races at that track.

Don

 

[Marshall Barton]

Consecutive races at each track. For example, Talladega

had 2 races in 2004,
one in April, one in October.

I have a 'RaceID' for each 'YearID' and I also have an

overall race number
starting with the first race back in 1949 up throught

present.

What I've done is query the winners, and then trying to

sort or group them
in a way that would show like my previous example, where

Earnhardt Jr. won
the fall race in 2001 at Talladega, both the spring and

fall race in 2002 and
the spring race in 2003. I want to be able to show that

he won 4 consecutive
races at that track.

As a human, I can understand what you're saying, but how is
a computer supposed to know there aren't any Summer races or
that they didn't have a race one Fall?

You have to have some data in each record that can be used
in an algorithm to determine if two races are consecutive.
For example, the simplest way would be a field that numbers
the races at a track from 1 to 90 (or whatever). If your
OverallRace number does this, then you can use a sequence of
querys like:

qryEnds:
SELECT A.RaceNo AS SeqStart,
(SELECT Min(T.RaceNo)
FROM Races AS T LEFT JOIN Races AS X
ON T.RaceNo + 1 = X.RaceNo
WHERE A.RaceNo<= T.RaceNo
AND X.RaceNo Is Null
AND T.Track = "Talladega"
AND X.Track = "Talladega"
) AS SeqEnd
FROM Races As A
WHERE Track = "Talladega"

qryStreak:
SELECT SeqEnd - Min(SeqStart) + 1
FROM qryEnds
GROUP BY SeqEnd