Return only top 10

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I'm trying to write a query that only returns the top 10
customer sales. Do I need to use a report to do this?
Currently I run a query that shows all sales and then I
export to Excel and sort and cut out the top 10 to a new
sheet. I want to be able to get just the top 10 customers
without all the extra steps. Pleas advise. Todd.
 
Todd said:
I'm trying to write a query that only returns the top 10
customer sales. Do I need to use a report to do this?
Currently I run a query that shows all sales and then I
export to Excel and sort and cut out the top 10 to a new
sheet. I want to be able to get just the top 10 customers
without all the extra steps. Pleas advise. Todd.
Click to expand...

In the property sheet for the query is an option (Top Values) to return a fixed
number of rows. Set that to 10. It will require that you apply a sort on enough
fields to determine which 10 records should be returned. You can get more than 10
records if there are ties.
 
If you are using the query by example grid then bring up the query property
sheet and enter 10 in the "Top Values" field. Alternatively edit the SQL by
putting " Top 10 " immediately after "SELECT". You must make sure that the
query is sorted as you want for this to work properly - ie sort in
descending order to get the 10 highest values or in ascending order to get
the 10 lowest values.
 
Open your query in designview. On your query design
toolbar you should have a "Top Values" drop-down menu (it
probably says "All" right now). Type in the value 10, and
it should fix it.

Isbjornen
 
That does work but it seems to not work for just one field
only for the whole query. I want it to pull every customer
but the top 10 products bought by each. When I run it just
pull the first 10 customers.
 
One method is to use nested queries.

QryOne:
SELECT CustomerID, ProductsID, SUM(Quantity) as NumberBought
FROM TablePurchases
GROUP BY CustomerID, ProductsID

Then you can use that in another query.

SELECT Q.CustomerID, Q.ProductsID, Q.NumberBought
FROM QryOne as Q
WHERE Q.ProductsID In
(SELECT TOP 10 Tmp.ProductsID
FROM QryOne as Tmp
WHERE Tmp.CustomerID = Q.CustomerID
ORDER BY Tmp.NumberBought Desc, Tmp.ProductsID)

If you want ties, then drop the Tmp.ProductsId from the ORDER BY clause.
 
Back
Top