Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    8

    Unanswered: Display x Numbers or Rows at a time [Resolved]

    Hi,
    I need to show 15 records a one page in a report, also have the sum of that 15 records in each page. Is there any sql command to do that. I am using VB with ADO and ActiveReport as the reporting tool.

    Any help is appriciated.

    Danial
    Last edited by Danial; 06-16-03 at 15:39.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    I was able to produce a report that groups 15 records per page, with subtotals for each group. The requirements are that you have an autonumber field in you table of items you will be reporting, and all items pulled by your query are numbered sequentially (in the autonumber field) starting with 1.

    I based the report on the following 2 queries:

    Query5:
    SELECT Table3.Key, Table3.CUSTNO, Table3.ITEM, Table3.Charge, Int(([KEY]-1)/15)+1 AS Group15
    FROM Table3;

    Query6:
    SELECT Query5.Group15, Query5.CUSTNO, Query5.ITEM, Sum(Query5.Charge) AS [Item Charge]
    FROM Query5 GROUP BY Query5.Group15, Query5.CUSTNO, Query5.ITEM;

    The calculation Int(([KEY]-1)/15)+1 calculates a value from the autonumber field I called KEY; the first 15 items get the value 1, the next 15 get 2...etc., and the report is grouped on this calculated field.

    A small database is attached so you can see how the report, which groups and subtotals every 15 items, was set up.

    Jerry
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2002
    Posts
    8
    Hi Jerry,
    Thanks ever so much for taking the time to answer my question. What you have suggested looks cool, but dont know if it will be full proof though. I my primary key is autonumber but its not sequential as your soloution requires it to be.

    I have found another way of doing it in active report. You can force active reprot to start a new page after x number of recrods (using code). That seems to work for me.


    Thanks again.

    Danial

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    wouldn't int(key/15) do ethe same thing? only the first 15 get 0
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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