Results 1 to 5 of 5

Thread: Limit Rows Returned In Access Query

  1. #1
    Join Date
    Nov 2008
    Posts
    69

    Limit Rows Returned In Access Query

    hi there

    I am executing a query which will return some summary data from a table in Access. I have built the query with the idea of driving an Access report, but I want to insert a limit the rows returned to the first 20 rows. I have also tried WHERE ROWNUM < 21, but using it into the SQL of the Access query does not work. What can I use here to limit the number of rows returned? This is running in Access and not ASP. The SQL is included below:

    SELECT TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1, Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) AS TOT_ORDERED, Sum(TBL_D02_PROG_4_SUMMARY.ORDER_TOTAL_PRICE) AS SUM_PRICE
    FROM TBL_D02_PROG_4_SUMMARY
    GROUP BY TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1
    ORDER BY Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) DESC;

    Please help me regarding this

    your views will be appreciated

  2. #2
    Join Date
    May 2008
    Posts
    2,389

    Re: Limit Rows Returned In Access Query

    SELECT TOP 20 TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1, Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) AS TOT_ORDERED, Sum(TBL_D02_PROG_4_SUMMARY.ORDER_TOTAL_PRICE) AS SUM_PRICE
    FROM TBL_D02_PROG_4_SUMMARY
    GROUP BY TBL_D02_PROG_4_SUMMARY.CUST_ID, TBL_D02_PROG_4_SUMMARY.NAME1
    ORDER BY Sum(TBL_D02_PROG_4_SUMMARY.QTY_ORDERED) DESC;

  3. #3
    Join Date
    Feb 2008
    Posts
    1,852

    Re: Limit Rows Returned In Access Query

    SELECT TOP 5 column1, column2, dt FROM foo ORDER BY dt DESC


    For SQL Server 6.5, you'll have to take a slightly different approach, utilizing the SET ROWCOUNT functionality (this will not work in Access):
    SET ROWCOUNT 5
    SELECT column1, column2, dt FROM foo ORDER BY dt DESC
    SET ROWCOUNT 0 -- (This last step is the most important!

  4. #4
    Join Date
    Jan 2008
    Posts
    1,521

    Re: Limit Rows Returned In Access Query

    It isn't something that is available at the configuration level. You may want to double check that it does what you want it to do anyway: see the javadoc for setMaxRows. With Oracle it is still going to fetch every row back for the query and then just drop the ones outside the range. You would really need to use rownum to make it work well with Oracle and you can't do that either in the configuration.

  5. #5
    Join Date
    Apr 2008
    Posts
    1,948

    Re: Limit Rows Returned In Access Query

    If you know you will be dealing with only one table, then define a view with rownum in the where statement to limit the number of rows. In this way, the number of rows is controlled at the DB and does not need to be specified as part of any query from a client application. If you want to change the number of rows returned, then redefine the view prior to executing query.

    A more dynamic method would be to develop a procedure and pass in a number of rows, and have the procedure return a ref_cursor to your client. This would have the advantage of avoiding hard parsing on the DB, and increase performance.

Similar Threads

  1. How to run Query Automatically on MS Access
    By Rixwel in forum Software Development
    Replies: 3
    Last Post: 18-08-2009, 10:24 AM
  2. Number of rows returned by SQL query
    By odumitre in forum Software Development
    Replies: 2
    Last Post: 12-08-2009, 05:11 PM
  3. How to limit internet access windows xp ?
    By Yatish in forum Technology & Internet
    Replies: 3
    Last Post: 11-07-2009, 01:28 PM
  4. Replies: 2
    Last Post: 09-07-2009, 08:57 AM
  5. Help in Access query
    By $tatic in forum Software Development
    Replies: 4
    Last Post: 20-12-2008, 05:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,721,084,998.92213 seconds with 17 queries