Count or Sum consecutives values

  • Thread starter Thread starter hectorleo
  • Start date Start date
H

hectorleo

Hi:
I use access in horse racing records, and I need to know
how make a query to sum or count consecutive wins for
racing horses, jockeys, trainers and owners.
 
Hi,


Rank their participation, A
Rank their win, over time, B
Group By on A-B, COUNT(*) give consecutive wins.

With details, since RANKing is not a predefined SQL operation we first have
to do it before making the desired operations:

Races ' table name
RaceDate, RaceSomeInfo, IsAWin ' fields


to get the ranks:


SELECT RaceSomeInfo,

( SELECT COUNT(*)
FROM Races as b
WHERE b.RaceSomeInfo=a.RaceSomeInfo
AND b.RaceDate <= a.RaceDate) As A,

( SELECT COUNT(*)
FROM Races as b
WHERE b.RaceSomeInfo=a.RaceSomeInfo
AND b.RaceDate <= a.RaceDate
AND b.IsAWin ) As B

FROM Races AS a



That should supply the basic information, let us save it under the name Q1.
We then make a second query like:



SELECT RaceSomeInfo, MAX(seq)
FROM (
SELECT RaceSomeInfo, COUNT(*) As seq
FROM q1
GROUP BY RaceSomeInfo, A-B
)
GROUP BY RaceSomeInfo


to get the maximum sequence of wins, accordingly to the field RaceSomeInfo
(horse, owner, trainer, whatever).




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top