Just the Top 20

[Robert Gillard]

I have a query that returns the number of items sold by each member of
staff. There are over 500 staff and really I only want to know the names and
number of items sold by the top 20 staff.

How can I amend my basic query to just show the top 20 names (and can you do
the same for the last 20?)

With thanks

Bob

 

[Rick Willingham]

Robert

I'm a new user also. Your question reminded me of another post I had read here, so I did a search on the word "Top" and got several answers. Allow me to suggest that you do the same - you'll find more info on your subject, it's much faster than waiting for a response, and it sometimes reveals questions that you might not have thought of.

One of the answers I came across suggests to have the SQL begin "SELECT TOP 20 ". Beware that if you simply type the words "TOP 20" in, you get the top 20 of the first field in your query (it might be an ID field). The other posts go into more detail...

I tried this on the DB I'm working on and it works great, but I have the query filling a listbox that re-sorts when the user clicks a "column header" button, so the listbox no shows the top dates, the top first names, the top last names, etc. Also, the function I'm using sorts first by ascending then the next click sorts by descending, then ascending...

I can imagine that you might want to have a "Top 20" for number of items, value of items, % of increase, etc

Hope this helps..

----- Robert Gillard wrote: ----

I have a query that returns the number of items sold by each member o
staff. There are over 500 staff and really I only want to know the names an
number of items sold by the top 20 staff

How can I amend my basic query to just show the top 20 names (and can you d
the same for the last 20?

With thank

Bo

 

[Douglas J. Steele]

Assuming you're doing this through the query builder, open the query in
Design mode and look at its properties. One of the first properties listed
will be Top Values (and presumably it'll be set to All). Type 20 there, and
make sure your query is sorted in Descending order. To get the bottom 20,
sort in Ascending order.