Control output to report based on subtotal

[StormcrowRahl]

I need to cause a page break when a group subtotal is equal to or not exceeding 20.

Setup: a parameter query is run with user input, which is where the report gets its information. Now, I can subtotal the specific column(#ofdocuments) in the report without any problem. But I need the report to page break once the running sum of the column(#ofdocuments) gets to 20 but doesn't exceed 20.

Example 1... The first 5 records all have a value of 4 in (#ofdocuments)column, only those 5 records should print on the first page of the report.

Example 2... The first 7 record's columns (#ofdocuments) have a sum of 17, and the 8th record has a value of 4 in the (#ofdocuments) column. Since this 8th record would make the running sum of the (#ofdocuments)column exceed 20, only the first 7 records should be printed on the page.

Thanks for any help.

 

[Remou]

I think you will need a query with a running sum of number of documents, which you can evaluate with an immediate If to give a further group, Page 1 or Page 2. Here is a very rough sketch of this idea:
[tt]SELECT tblDocs.GroupA, tblDocs.DateDoc, tblDocs.NoOfDocs, IIf((SELECT Sum(NoOfDocs) From tblDocs A Where A.GroupA=tblDocs.GroupA And A.DateDoc<=tblDocs.DateDoc)<20,"P1","P2") AS PageNo
FROM tblDocs
ORDER BY tblDocs.GroupA, tblDocs.DateDoc;[/tt]