Limit "Top 5" Recordset to 5 Records

  • Thread starter Thread starter Bob Barnes
  • Start date Start date
B

Bob Barnes

If I ask for a "Top 5" & if there is a
tie for 5th Place for 3 categories, the recordset will
return 7 Records.

I am sending that data to Excel where there are several
"5-row Named Ranges" to handle that. If I send 7 Records,
it spills down into the "Title" for another Named Range.

I handled this problem where Zeroes created more than 5
Records with a WHERE > 0.

How can I ensure I get a "Top 5" when there could be a tie
for 5th Place where 12 Records tie for 5th with values of
2? This would result in 16 Records. Technically those 12
Records all deserve to be "5th Palce". Maybe a "truncate"?

TIA - Bob
 
Realistically, you can't. That's how "Top 5" queries work in Access.

You could, I suppose, write the results of the query to a temporary table,
and then delete all but 5 rows. If you're going to do that, you might be
interested in http://www.granite.ab.ca/access/temptables.htm that Tony Toews
has. It shows you how to create temporary tables in a temporary database, so
that you don't get unnecessary growth in your production database.
 
Doug, thank you.

I was afraid of that. I've got great code from "SteveT"
which writes about 20 Recordsets to a well-organized
display in Excel. I guess I could open up more blank
rows between the presented 2 by 6 (top 5 plus a Title)
data. I can stop the "Zero-ties". The probability of
getting a lot of non-zero ties is not high.

I know Tony has great info, & I will check it.

Thanks again, Bob
-----Original Message-----
Realistically, you can't. That's how "Top 5" queries work in Access.

You could, I suppose, write the results of the query to a temporary table,
and then delete all but 5 rows. If you're going to do that, you might be
interested in
Click to expand...
http://www.granite.ab.ca/access/temptables.htm that Tony
Toews
 
Why not creating a recordset of top5 in vba and copy the first five records
to a table. Now you have a recordset of only 5 top 5 to send to excel.



shadow
 
Shadow, thank you.

I agree. The only "problem" is the code must select only
one of the "ties" (probably the 1st ascending). But, in
our analysis, it will be important to note "other 5th
Places".

So, I will probably just allow more spacing between my
"2 by 6" - & should it overwrite something below that,
we'll note that data. Then use the backup to restore any
Titles overwritten.

Thanks again, Bob
 
Avoid TOP N syntax. Instead try this construct (courtesy of Joe Celko):

SELECT DISTINCT salary
FROM Personnel P1
WHERE 5 > (SELECT COUNT(*)
FROM Personnel P2
WHERE P1.salary < P2.salary)
 
Another approach, similar to the Celko method:

Total of 2 queries.

First query does nothing more than a "group by" on the
required fields. Select the top 5 values. You should
return 5 distinct values always.

Then perform a join between this aggregate and the main
table, turn on aggregration, and take the First result.

David Atkins, MCP
 
onedaywhen, thank you. Always like to try to learn
different ways of getting the job done.

Bob
 
GreySky, thank you. Always like to try to learn different
ways of getting the job done.

Bob
 
Back
Top