Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unanswered: limiting number of rows in select

    hi,

    i want to be able to limit the number of rows returned in a select.

    i know top can be used, but this will get me only the first 'x' rows.

    I need to be able to do the following:
    get rows 1-20, then 21-30, 31-40 etc.

    any ideas appreciated

    thanks

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Could you further explain what you are trying to do? Is it correct that you want to select 20 rows, then 10, then 10, etc., or did you mean 10 records at each stage.

    What do you expect to see in the select query results? Are you asking if a select query can display sets of 10 records each in stages?

    If your goal is to display records only in sets of 10, are the rows in your table numbered. If so, you could put a prompt in your query to accomplish this?

    Jerry

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try searching for CACHE RECORDSET ... on the ADO and DAO Recordsets there is a Cache option to limit the # of records returned at 1 time ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2005
    Posts
    2
    Quote Originally Posted by JerryDal
    Could you further explain what you are trying to do? Is it correct that you want to select 20 rows, then 10, then 10, etc., or did you mean 10 records at each stage.

    What do you expect to see in the select query results? Are you asking if a select query can display sets of 10 records each in stages?

    If your goal is to display records only in sets of 10, are the rows in your table numbered. If so, you could put a prompt in your query to accomplish this?

    Jerry

    hi,
    my goal is to display records only in sets of 10, however the rows are not numbered and i can't modify the table.

    any ideas?

    thanks

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I accomplished this in Access, displaying only 10 rows at a time, with two queries, and the second query uses the results of the first query. A small database with queries is attached. Query #2 prompts for a number. If you enter any number up to 10, you display rows 1-10. Enter a number between 11 and 20 to display rows 11-20. And so on.

    A condition must be met for this solution to run as expected. You must have a field in your table with no duplicate values. This field will be used by the first query to create a new field called RowNumber. I used the field SSN in my table.

    In the attached database, Query1 selects the columns to display, and generates a set of sequential numbers that will be used as row numbers. RunMe query selects rows from Query1, prompts for a number and uses that number to calculate which set of 10 rows will be displayed, with the ending row number being divisible by 10 except for the end of the database which may not have a full set of 10 records. I used the MOD function to calculate the starting and ending row, such as 1 and 10 when any number in that range is entered in the prompt.

    Query1 SQL:
    SELECT Person.SSN, Person.LastName, Person.FirstName, Person.MInitial, ["Enter a row number to display a set of 10 rows"] AS TargetSet,
    (SELECT Count(*) FROM Person AS T2 Where T2.SSN <= Person.SSN) AS RowNumber
    FROM Person
    ORDER BY Person.SSN;

    RunMe Query SQL:
    SELECT Query1.SSN, Query1.LastName, Query1.FirstName, Query1.MInitial, Query1.RowNumber
    FROM Query1
    WHERE (((Query1.RowNumber) Between ([Query1]![TargetSet]-(([Query1]![TargetSet]-1) Mod 10)) And ([Query1]![TargetSet]-(([Query1]![TargetSet]-1) Mod 10))+9));

    Jerry
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Correction: Note tha Query #1 is the query with the prompt, not the 2nd query.
    Jerry

Posting Permissions

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