#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2002
    Posts
    548
    Rep Power
    0

    MS Access - limit results to 5 results?


    What's the easiest way to limit the number of results in a MS ACCESS query?
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  4. #3
  5. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  6. #4
  7. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    1
    Rep Power
    0

    Question Specifying between top records & values


    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!
  10. #6
  11. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  12. #7
  13. No Profile Picture
    Average Intelligence
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2003
    Location
    Ohio/Chicago
    Posts
    678
    Rep Power
    12
    you are correct, sort by desc to get TOP x highest values.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0

    limit result query in oracle


    Example 1: Returning the first 100 rows from a table
    select * from employee where rownum <= 100

    Example 2: Returning the first 100 rows in order of salary from a table
    select * from (select * from employee order by salary desc) where rownum <= 100

    Note that the rownum query has to be wrapped around an inner select that actually does the order by.

    for more visit <snip>
    Last edited by r937; July 25th, 2013 at 06:19 AM.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by etechpulse
    select * from employee where rownum <= 100
    three points --

    1. it is generally considered poor etiquette to re-open dormant threads, and this one has been dead for ten years

    2. including links in the body of a reply instead of a signature file is frowned upon

    3. your solutions are crap, because MS Access does not support rownum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo