Force page after 20 records

  • Thread starter Thread starter Tia
  • Start date Start date
T

Tia

I have a report with a subreport. The report contains customer information
and the subreport contains systems we service for that customer. When I
print these reports, I only have room to print 20 records/systems per
customer. So I'm looking for suggestions on how to force a new page when the
number of systems exceed 20. When a new page is created, I would like the
customer information to show up on the new page.

Thank you for any suggestions.
 
One way is to use a "Ranking in a group" query to number records in each
group. Then use [Rank]\20 for Sorting and grouping in the report and
force a new page on group.
 
Thanks for the suggestion. However, I'm not understanding how I go about
this. Will you explain a little more on how to accomplish this?

Thank you.
KARL DEWEY said:
One way is to use a "Ranking in a group" query to number records in each
group. Then use [Rank]\20 for Sorting and grouping in the report and
force a new page on group.
--
KARL DEWEY
Build a little - Test a little


Tia said:
I have a report with a subreport. The report contains customer information
and the subreport contains systems we service for that customer. When I
print these reports, I only have room to print 20 records/systems per
customer. So I'm looking for suggestions on how to force a new page when the
number of systems exceed 20. When a new page is created, I would like the
customer information to show up on the new page.

Thank you for any suggestions.
Click to expand...
Click to expand...
 
This is a ranking in a group query --
SELECT Q.[Group], Q.[Item_no], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points] DESC;

It numbers the records in each group and starts numbering again for the next
group.

--
KARL DEWEY
Build a little - Test a little


Tia said:
Thanks for the suggestion. However, I'm not understanding how I go about
this. Will you explain a little more on how to accomplish this?

Thank you.
KARL DEWEY said:
One way is to use a "Ranking in a group" query to number records in each
group. Then use [Rank]\20 for Sorting and grouping in the report and
force a new page on group.
--
KARL DEWEY
Build a little - Test a little


Tia said:
I have a report with a subreport. The report contains customer information
and the subreport contains systems we service for that customer. When I
print these reports, I only have room to print 20 records/systems per
customer. So I'm looking for suggestions on how to force a new page when the
number of systems exceed 20. When a new page is created, I would like the
customer information to show up on the new page.

Thank you for any suggestions.
Click to expand...
Click to expand...
Click to expand...
 
I have a report with a subreport. The report contains customer information
and the subreport contains systems we service for that customer. When I
print these reports, I only have room to print 20 records/systems per
customer. So I'm looking for suggestions on how to force a new page when the
number of systems exceed 20. When a new page is created, I would like the
customer information to show up on the new page.

Thank you for any suggestions.
Click to expand...

Add a line break control to the bottom of the Detail section of your
report.
Add an unbound control to the detail section.
Set it's control source to
=1
Set it's Running Sum property to Over All (or Over Group ... see *
below)
Name this control "CountRecords".
You can make it not visible if you don't wish to actually show the
record count.

Code the DetailFormat event:
Me.LineBreakName.Visible = [CountRecords] Mod 20 = 0

Change [LineBreakName] to whatever the actual name of the line break
control is.

* If you wish to start each Customer on a new page, then in Report
Design View, click on View + Sorting and Grouping. Enter the
[CustomerName] field in the Field/Expression column. In the lower
panel, set the GroupHeader property to Yes.
Save the changes.
Then in Report Design View, set the GroupHeader's ForceNewPage
property to BeforeSection.

Each Customer will start on a new page. Any customer with more than 20
records will also be split over several pages.
Change the Running Sum (above) from Over All to Over Group.
 
I have tried adding the line break control and unbound control, following
your instructions to both the report and the subreport. When I add to the
report, nothing happens. When I add to the subreport, it forces each system
onto its own page with only the first system having the customer name and
address. Is there something I should be looking for that I may be doing
wrong??? I really appreciate all of your suggestions so far and I'm thinking
that this is what I've been looking for but that I'm not getting something
coded right.

Thanks again!
 
Back
Top