MS Access - limit results to 5 results?

What's the easiest way to limit the number of results in a MS ACCESS query?

In the query properties or on the Query Design toolbar, change the Top Values property from "All" to 5.

You can limit the resultset to either a hardcoded count or a percentage.

I think we're talking apples and oranges here. When I run a query, I get back the records that meet that query's criteria. What are "RESULTS" if they're not records. I must be misunderstanding exactly what you're trying to do.

I opened a query on a table with 3935 records. I set the Top Value = 5. No matter how I changed the range on my WHERE clause, it always returned 5 records from within that range. Obviously it was looking at the whole table, not the first 5 records.

If Top Value only looked at the first 5 records, this would be a totally useless feature on a table of any substantial size. The probability that you would find the records that matched your query's WHERE clause in the first 5 records is about nil. Also, the only way to guarantee that records will always return from a query in a particular order is by using an ORDER BY clause. The concept of "the first 5 records" in a table doesn't necessarily make sense since you have no way of knowing which order the database engine has stored the records on the disk.

According to the documentation and the way it works on my machine, Top Value is the feature you're looking for. I don't know why it isn't working that way on your machine.

Documentation:
You can use the TopValues property to return a specified number of records or a percentage of records that meet the criteria you specify. For example, you might want to return the top 10 values or the top 25 percent of all values in a field.

When running a query using the SELECT TOP

SELECT TOP 5000 [cons 5k 6-10].[Acct Nbr], [cons 5k 6-10].[MaxOfShipped Ord], [cons 5k 6-10].[MaxOfShipped Rev]
FROM [cons 5k 6-10] LEFT JOIN [cons 5k 6] ON [cons 5k 6-10].[Acct Nbr] = [cons 5k 6].[Acct Nbr]
WHERE ((([cons 5k 6].[Acct Nbr]) Is Null))
ORDER BY [cons 5k 6-10].[MaxOfShipped Rev] DESC;

The query results will often receive anywhere from 5,000 to 5,045 records as it breaks at 5,000 VALUES not 5,000 RECORDS (record = row).

How do I pull only 5,000 records while using SELECT TOP? It is required to have both elements in the query: SELECT TOP and a record maximum/limit.

Thanks!

If you take the TOP 5000 out of the query do you get the results you're looking for? In other words, since you're sorting the data in descending order and limiting the query to 5,000 records shoudn't this give you the top records even with the TOP 5000 removed from the query?

I'm not at a computer where I can test this so I don't know if it is going to sort the data descending first and then give you 5000 records (correct results) or if it is going to take 5000 records and then sort them in descending fashion (incorrect results).

This is just a guess so let me know if it doesn't work.

you are correct, sort by desc to get TOP x highest values.