Solved Access VBA Grouping and Averages (1 Viewer)

[3DogLegs]

Continuing saga of my horse racing project.
I am using Access 2010 and Excel 2010.
I have attached a small database with a table and 2 queries.

'Table1' has the following fields:
MtgDate, Race, Margin (in metres), Rate

For each Race I want to get the average Rate for Top 6 Horses that finish within 'x' metres of the winner (including the winner)
For different Meetings and Race types, 'x' can vary between 4m and 7.5m

Using Query01 I can get the Top 6 (or less) Horses that finish within 'x' metres of the winner

In Query02 I use Query01 to get the average for each Race, and the number of horses that finish within 'x' metres.

Now is it possible to do all this in a single query if I hard code the 'x' figure?
Second is it possible to achieve all this using a parameter in a single query?

 

[ebs17]

PARAMETERS
   parMargin Double
;
SELECT
   Q.Race,
   Round(AVG(Q.Rate), 10) AS AvgRate,
   COUNT(Q.Rate) AS Runners
FROM
   (
      SELECT
         T.Race,
         T.Margin,
         T.Rate
      FROM
         Table1 AS T
      WHERE
         T.Margin IN
            (
               SELECT TOP 6
                  Margin
               FROM
                  Table1 AS MR
               WHERE
                  MR.Race = T.Race
                     AND
                  MR.Margin <= parMargin
               ORDER BY
                  MR.Margin,
                  MR.Rate
            )
         ) AS Q
GROUP BY
   Q.Race
;

Sorting in Query01 was not adopted in Query02 due to irrelevance.