Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7

Thread: MS access query

  1. #1
    Regular Coder ellisd5's Avatar
    Join Date
    Jun 2002
    Location
    Uk
    Posts
    160
    Thanks
    5
    Thanked 0 Times in 0 Posts

    MS access query

    I do a select of my access database and bring back all the records. So currently like this
    Code:
    SELECT
        value1, value2
    FROM
        myTable
    ORDER BY
        value2 desc
    What I want to add to this is only returning 20 rows, how can I do this?

    Thanks Dale
    Dale Ellis
    __________________

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    SELECT TOP 20
        value1, value2
    FROM
        myTable
    ORDER BY
        value2 desc
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #3
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS access query

    Originally posted by hharchester

    What I want to add to this is only returning 20 rows, how can I do this?
    Just incase: do you mean the 20 top rows or only 20 rows per page (like 1-20; 21-40; 41-60 and so on)? In that case it is a recodset paging that you're trying to do... It is a bit more complicated but not that much...

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    By using TOP 20 , there is absolutely no guaranty that you wount get more then 20 records.
    The reason is that TOP doesn't cut off if you have equal values for the 20°, 21°,22° etc values.

    So if you would have a value2 distribution like

    1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,...

    then you would get 34 records or so, not 20.

    If you realy need a maximum of 20 records, then you need an additional loop inside your ASP code, where you loop through the records until you reach the 20° (using an incrementing counter-variable).

  • #5
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    You need recordset paging like jeskel suggested.
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #6
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the ASP101 code is excellent... I started with this one. However, you might benefit from the getrows method. Check this thread and the two links provided by raf: http://www.codingforums.com/showthre...threadid=29360

  • #7
    Regular Coder ellisd5's Avatar
    Join Date
    Jun 2002
    Location
    Uk
    Posts
    160
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Thumbs up

    Thanks for all the help, I have it working how I want now
    Dale Ellis
    __________________


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •