[AccessD] Totals Query: Top Predicate

Mark,

    You wish to get top 5 values per group in a totals query.

    Subquery based solution, using In clause, as given in the knowledgebase article, appears to work only with normal select queries. If applied to a totals query, it fails to return any record.

    Sample SQL given below, should be able to cater to your needs. It uses a subquery to rank the records in each group, as per comparative total values. This method suits both types of queries, i.e totals query (involving group by clause), as well as normal select query.

With best wishes,
A.D.Tejpal
--------------

Note - Q_Sales is the name of Totals query, fetching
            TotSales (Sum of sales) grouped by Year (PYear)
            and month (PMonth). 
            Sample SQL below, gets top 5 values of 
            TotSales for each year.
=================================
SELECT Q_Sales.*  FROM Q_Sales 
WHERE ((((Select Count(*) From Q_Sales As Q1 Where Q1.PYear = Q_Sales.PYear And  (Q1.TotSales > Q_Sales.TotSales Or (Q1.TotSales = Q_Sales.TotSales And Q1.PMonth <= Q_Sales.PMonth)))) <= 5)) 
ORDER BY Q_Sales.PYear, Q_Sales.TotSales DESC;
=================================

  ----- Original Message ----- 
  From: Admin Sparky 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, February 10, 2005 00:27
  Subject: Re: [AccessD] Totals Query: Top Predicate


  Jim, group,

  I'm having difficulties correlating M$'s subquery example...
  ***************
  Add the following fields to the query grid:
  Field: CategoryName
  Sort: Ascending

  Field: ProductName

  Field: UnitsInStock
  Sort: Descending
  Criteria: In (Select Top 3 [UnitsInStock] From Products Where _
  [CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc) 
  ***************

  ...to my original query.  The goal is to return only the top 5
  [Sum([qry1].Errors) AS SumOfNumberOfErrors] for each [InputYear] /
  [InputMonth] combination.  The below query returns all of the
  necessary records, I just need to limit the results.  Am I having
  difficulties because I'm attempting this with a totals query?

  SELECT         [qry1].InputYear,
                      [qry1].InputMonth,
                      [qry1].fkChecklistItem,
                      Sum([qry1].Errors) AS SumOfNumberOfErrors,
                      [qry1].ByMonthYear,
                      [qry1].[CODE DESCRIPTION]
  FROM            [qry1]
  GROUP BY    [qry1].InputYear,
                      [qry1].InputMonth,
                      [qry1].fkChecklistItem,
                      [qry1].ByMonthYear,
                      [qry1].[CODE DESCRIPTION]
  ORDER BY    [qry1].InputYear,
                      [qry1].InputMonth,
                      Sum([qry1].NumberOfErrors) DESC;


  Methinks I need a bit of hand-holding on this one:(  I could achieve
  this via temp tables as suggested, but I would really prefer to learn
  from this.

  Many TIA,


  Mark




  On Mon, 7 Feb 2005 14:40:16 -0500, Jim Dettman <jimdettman at earthlink.net> wrote:
  > See:
  > 
  > http://support.microsoft.com/default.aspx?scid=kb;en-us;210039
  > 
  > Jim
  > 
  > 
  > -----Original Message-----
  > From: accessd-bounces at databaseadvisors.com
  > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Admin Sparky
  > Sent: Monday, February 07, 2005 1:47 PM
  > To: accessd at databaseadvisors.com
  > Subject: [AccessD] Totals Query: Top Predicate
  > 
  > Group,
  > 
  > I have a totals query grouped by year, then month.  I would like to > return the top 5 records for each year/month combination.  What are my > options?  At the moment, the top predicate merely returns the top 5 records for the first year/month combination.
  > 
  > Mark Mitsules