Query : Top 5

G

Gargamil

I'm trying to get a query to return a set of results with a slightly unusual
result. The result of the query is to be the top 5 results for of the
records satisfying the criteria. As below

Coupe CarPrice $45,000
Coupe CarPrice $43,000
Coupe CarPrice $41,000
Coupe CarPrice $40,000
Coupe CarPrice $25,000
Sedan CarPrice $75,000
Sedan CarPrice $73,000
Sedan CarPrice $72,000
Sedan CarPrice $70,000
Sedan CarPrice $65,000

Now obviously I could run a Top5 query for each of the criteria sets and
then combine, but isn't there a more elegant solution??

g
 
J

Jeff Boyce

Here's a pair of solutions posted by Duane -- you might have found this by
searching Google.com for the topic...

Build the report to sort and group by District and Profit. Place a text box
in the detail section:
Name: txtSequence
Control Source: = 1
Running Sum: Over Group
Then in code in the On Format event of the detail section
Cancel = Me.txtSequence > 5

Another method would be to use a subquery, something like (air sql):
SELECT *
FROM tblSales
WHERE ProductID IN (SELECT Top 5 ProductID FROM tblSales S WHERE
S.DistrictID = tblSales.DistrictID ORDER BY Profit DESC)
--
Duane Hookom
Microsoft Access MVP

Good luck

Jeff Boyce
<Access MVP>
 
Top