Tricky query

[Steve]

Throughout a season a horse will run on a number of
occasions. On each occasion that the horse runs it will
carry weight (measured in pounds). The horse will
probably carry many different weights throughout the
season.

I have a table that lists the following for each run:
Date
HorseName
Weight
Position

I want to create a table that shows me what the horse's
strike rate is, not only at a specific weight, but every
other weight below the specified weight. The final table
should show

HorseName
Weight
Wins
Runs

So if we saw an entry that said
HORSENAME WEIGHT WINS RUNS
HorseA 100 3 6

this would mean that HorseA has won 3 races out of the
six that he has contested carrying a weight of 100 or
less.

What is the most efficient way to come up with this data?

For your information the minimum and maximum weights
would be 98 and 180 respectively.

Thank you in advance

Steve

 

[Michel Walsh]

Hi,



SELECT a.HorseName, a.Weight,
- SUM( 1 = b.Position ) As Wins,
COUNT(*) As Runs

FROM myTable As a INNER JOIN myTable As b
ON a.HorseName = b.HorseName AND a.Weight >= b.Weight

GROUP BY a.HorseName, a.Weight



which is based on the fact that 1=b.Position returns 0 if false, -1 if true.
The sum of those comparisons would return the number of wins (with a
negative sign).



Hoping it may help,
Vanderghast, Access MVP