Filter top 5 values in filed

[Crocdundee]

using Access 2007
Hi I would like to filter the max 5 records in a group recordset
Ideally in each groupby i would like to present the top 50% (but that maybe too much to ask for )???
But I would like to filter the top 5 records in the group which is [Trckid]

SELECT Neurals.Trckid, Neurals.Track, Neurals.Rcno, Neurals.Tb, Max(Neurals.NR) AS MaxOfNR
FROM Neurals
GROUP BY Neurals.Trckid, Neurals.RcDate, Neurals.Track, Neurals.Rcno, Neurals.Tb
HAVING (((Neurals.RcDate)=[Forms]![FRMSummary]![cboDateFrom]));

I can filter all records in the froup and I think i have to use "IN" but not sure where it fits into the SQL.....[NR] is the filter field
Any help here will be appreciated
Regards
Graham

Trckid	RcDate	Track	Rcno	Tb	Horse	NR
41504Devo01	18/08/2013	Devonport Synthetic	1	5	Hoop	225
41504Devo01	18/08/2013	Devonport Synthetic	1	1	Crambazzled	177
41504Devo01	18/08/2013	Devonport Synthetic	1	2	Dhanraj	164
41504Devo01	18/08/2013	Devonport Synthetic	1	3	Hellova Street	84
41504Devo01	18/08/2013	Devonport Synthetic	1	4	Solo Man	76
41504Devo02	18/08/2013	Devonport Synthetic	2	11	Kenbiki	199
41504Devo02	18/08/2013	Devonport Synthetic	2	10	Beryl Bee	168
41504Devo02	18/08/2013	Devonport Synthetic	2	3	Costabitmore	133
41504Devo02	18/08/2013	Devonport Synthetic	2	5	Funambulism	122
41504Devo02	18/08/2013	Devonport Synthetic	2	8	Sharma Magic	106
41504Devo02	18/08/2013	Devonport Synthetic	2	2	Come On Charger	104

<tbody>
</tbody>

 

[Zack Barresse]

Go to the queries properties and set the Top property to 5. F4 will bring up the properties pane.

 

[Crocdundee]

Hi Zack thanks for the help
I have looked and can not find the "Top Property " you describe
I opened the query in design sql mode , hit F4 but i can for the love of me not find the "top Property"
I did find on the ribbon the Return Value when I have options like 5, 25 100 or 5% etc etc but that wont work
It bring up only 5 records, as I want 5 records in every Group

Graham

 

[SydneyGeek]

If you want to do this as a TOP n BY GROUP query, you will probably need to do it in 2 steps.
Here's an example that gets the top 5 invoice totals by client. There's a calculated field in the first query that adds the Invoice no * 0.000001 to generate a tie-break where one client has multiple invoices of the same value.

Start point: Invoice Totals with fields Company ID, Invoice No, Total (and other, irrelavent fields).
First query: qryInvoiceTotals Sorted

SELECT [Invoice Totals].[Company ID], [Invoice Totals].[Invoice No], [Invoice Totals].Total, [Total]+[invoice No]*0.000001 AS SortTotal
FROM [Invoice Totals]
WHERE ((([Invoice Totals].Total) Is Not Null))
ORDER BY [Invoice Totals].[Company ID], [Total]+[invoice No]*0.000001 DESC;

Second query: qryTop5InvoicesByClient

SELECT qryInvoiceTotalsSorted.[Company ID], qryInvoiceTotalsSorted.[Invoice No], qryInvoiceTotalsSorted.Total, (SELECT count(*) FROM qryInvoiceTotalsSorted AS q WHERE q.[Company ID] = qryInvoiceTotalsSorted.[Company ID] AND q.SortTotal >= qryInvoiceTotalsSorted.SortTotal) AS [Counter]
FROM qryInvoiceTotalsSorted
WHERE ((((SELECT count(*) FROM qryInvoiceTotalsSorted AS q WHERE q.[Company ID] = qryInvoiceTotalsSorted.[Company ID] AND q.SortTotal >= qryInvoiceTotalsSorted.SortTotal))<=5));

If you're not worried about ties you can do it in one step:

SELECT [Invoice Totals].[Company ID], [Invoice Totals].[Invoice No], [Invoice Totals].Total, (SELECT COUNT(*) FROM [Invoice Totals] AS q WHERE q.[Company ID]=[Invoice totals].[Company ID] AND q.Total>=[invoice Totals].total) AS [Counter]
FROM [Invoice Totals]
WHERE ((([Invoice Totals].Total) Is Not Null) AND (((SELECT COUNT(*) FROM [Invoice Totals] AS q WHERE q.[Company ID]=[Invoice totals].[Company ID] AND q.Total>=[invoice Totals].total))<=5))
ORDER BY [Invoice Totals].[Company ID], [Invoice Totals].Total DESC;

Denis