title: MS Access Top 5 Query multiple criteria grouped by month and area
body: <p>I would like to use the top 5 to capture the top 5 for a number of criteria.</p>
  
  <p>The data style is month date, area1, subarea1, sub areas2 etc etc, type, unique identifier for each row.</p>
  
  <p>What I'm trying to do is for each month and for area find the top 5 types.</p>
  
  <p>Sample Data:</p>
  
  <pre><code>
  Date              Division        Sub Division       ID 
  31/05/2012        Sales                              001
  31/05/2012        Sales           Call Centre        002
  31/05/2012        Sales           Call Centre        003
  31/05/2012        Sales           Store              004
  31/05/2012        Marketing                          005
  31/05/2012        Marketing        TV                006
  30/04/2012        Sales                              001
  30/04/2012        Sales           Call Centre        002
  30/04/2012        Sales           Call Centre        003
  30/04/2012        Sales           Store              004
  30/04/2012        Marketing                          005
  30/04/2012        Marketing        TV                006
  </code></pre>
  
  <p>And so on.</p>
  
  <p>The output I'm after is:
  Top 5 at Division level (no consideration of Sub Division) for each month
  Top 5 at each Sub-Division level for each month</p>
  
  <p>Sample Output:
  <code><pre>
  Date          Top5Areas     Volume
  31/05/2012    Sales         100
  31/05/2012    Marketing     90
  31/05/2012    HR            50
  30/04/2012    Sales         100
  30/04/2012    Marketing     90
  30/04/2012    HR            50
  </code></pre>
  And depending on the level the sub division for the respective area:
  <code><pre>
  Date          Top5Areas     Volume
  31/05/2012    Call Centre   100
  31/05/2012    Store         90
  31/05/2012    HR            50 
  </code></pre>
  but with 5 for each group.</p>
  
  <p>I'm not should how to do this and wonder if I'll need to build up the result through a number of queries or whether there is a better way.</p>
  
  <p>Thanks in advance and appreciate your suggestions.</p>
  
  <p><code><pre>
      SELECT [TableA].[DisplayVar] AS DisplayVar, [TableB].[Date] AS MonthDate, TableC.        [Divison] AS Divison, Volume
      FROM (SELECT [TableA].[DisplayVar],
      [TableB].[Date],
      TableC.[Divison],
      Volume
        (  SELECT  COUNT(<em>) + 1
        FROM    (   SELECT [TableA].[DisplayVar], [TableB].[Date], TableC.[Divison], COUNT(</em>)     AS Volume
           FROM    ([TableA] INNER JOIN [TableB] ON [TableA].[ID] = [TableB].[ID]) LEFT JOIN     TableC ON [TableB].ID = TableC.Descriptor
           GROUP BY [TableA].[DisplayVar], [TableB].[Date], TableC.[Divison]
        ) AS T
        WHERE   T.[TableB].[Date] = Data.[TableB].[Date]
        AND     T.Volume > Data.Volume
      ) AS Rank
      FROM    (   SELECT  [TableA].[DisplayVar], [TableB].[Date], TableC.[Divison], COUNT(*)     AS Volume
        FROM    ([TableA] INNER JOIN [TableB] ON [TableA].[ID] = [TableB].[ID]) LEFT JOIN     TableC ON [TableB].ID = TableC.Descriptor
        WHERE   [TableB].[Date] BETWEEN Date() AND DateADD("m", -12, Date())<br>
      GROUP BY[TableA].[DisplayVar], [TableB].[Date], TableC.[Divison]
      ) AS DATA
      )  AS Data
      WHERE (((Data.Rank)&lt;=5))
      ORDER BY [TableB].[Date] DESC , Volume DESC;
  </code></pre></p>