Filter top 1 in field

[Crocdundee]

Hi I made this query qith the following sql

SELECT RS.[Rcdate], RS.[Trk], RS.[Rn], RS.[Tab], RS.[Horse], RS.[APrice], RS.[CP]
FROM RS
WHERE (((RS.[Rcdate])=#11/20/2015#) AND ((RS.[APrice])<25))
ORDER BY RS.[Trk], RS.[Rn], RS.[CP] DESC;

I wish to filter the top 1 in [cp]
grouped by [rsdate], [trck], [rn]

any help here would be appreciative
Graham
and [aprice] stays same at <25

 

[Joe4]

Try this:

SELECT RS.[Rcdate], RS.[Trk], RS.[Rn], RS.[Tab], RS.[Horse], RS.[APrice], RS.[CP]
FROM RS
 INNER JOIN 
    (SELECT RS1.Rcdate, RS1.Trk, RS1.Rn, Max(RS1.CP) AS MaxOfCP 
    FROM RS AS RS1 WHERE (RS1.Rcdate=#11/20/2015#) And (RS1.[APrice]<25) 
    GROUP BY RS1.Rcdate, RS1.Trk, RS1.Rn)  AS Q1 
ON (RS.Rcdate=Q1.Rcdate) AND (RS.Trk=Q1.Trk) AND (RS.rn=Q1.rn) AND (RS.cp=Q1.MaxOfCP);

 

[Crocdundee]

Thank you Joe4 for your help it works perfectly and I would never been able to do it.
I applaud all you Programmers whom dedicate your time and knowledge to both Excel and access
Thanks
Graham

 

[Joe4]

Your welcome!

As opposed to using the Subquery method I used above, you could do the same thing in a series of two queries.
The first one would just be an Aggregate Query which groups by the fields you mentioned and takes the Max CP value.
Then link that Aggregate Query back to your original table, linking on all the fields in the Aggregate Query, and returning all the fields from the original query that you want.

Though it is two queries, the advantage to this other approach is that it could be done entirely with the Query Builder and not require you to explicitly write your own SQL code.

 

[Crocdundee]

Yes That I will try , another question please how can I change the SQL to list top 3 in [cp]

Regards
Graham

 

[Joe4]

Use the method described here: How to select top 10 in Access query? - Stack Overflow
You may have to incorporate it with the methods I described above.

If you run into some issues, please post a data sample and expected output (I am not 100% clear if your want the top 3 before or after your grouping, but an example will show us clearly what you are after).