Search code examples
databasems-accessselectlimit

how to use LIMIT in query of MS ACCESS 2007

I have created some queries in MS Access 2007. they are giving error if I use LIMIT in query. Can anyone help me out in this? How to use LIMIT in MS Access 2007 query?

Solution

  • There is no LIMIT keyword in Access (if you use the JET engine). You can use TOP x to give the first x results. Usage:

    SELECT TOP 5 id FROM users ORDER BY joindate

    From Microsoft Jet Database Engine Programmer's Guide - Chapter 4:

    TOP N and TOP N PERCENT Predicates

    Although you can use the WHERE and HAVING clauses to filter the selection of records, sometimes this isn't sufficient. For example, you may want to select all records where the state is CA, but only see the orders for the top 10 customers. Microsoft Jet provides TOP N and TOP N PERCENT predicates to limit the presentation of records after they're selected.

    TOP N Predicate

    You can use the TOP N predicate to specify that your query return only a specific number of records to your program: