[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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>