Force page every 20 records.

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

Tia

I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
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.
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 doubt a subreport understands anything related to pages.

I think you had another suggestion that involved determining the groups of
20 in the subreport's record source so that you can create a calculated
column where each group of 20 records has its own unique value from other
groups of 20. You would then be able to create separate instances of the
subreport for each group of 20.

--
Duane Hookom
Microsoft Access MVP


Tia said:
I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
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.
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.
Click to expand...
 
Could you not use a Rank by Grouping query as I posted on 17 March?

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.

Then use the report Sorting and Grouping = Rank\20 and force new page on
new group.

Tia said:
I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
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.
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.
Click to expand...
 
I wasn't clear on how to do this so I'm not sure that I did it right, but I
couldn't get it to work....Is there somewhere that I can learn more about
this?? I think it might be my best shot but I'm thinking that I'm not
entering it correctly so its not working...



KARL DEWEY said:
Could you not use a Rank by Grouping query as I posted on 17 March?

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.

Then use the report Sorting and Grouping = Rank\20 and force new page on
new group.

Tia said:
I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
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.
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.
Click to expand...
Click to expand...
 
Okay, I've been reading through all my access "bibles" trying to figure this
out...However, I'm getting more and more confused. So here's my layout.
I've got a report and subreport-they are linked by Customer ID and Customer
Address. The subreport lists all systems connected to the Customer ID. I
have a few Customers that have more than 20 systems so I need to create a new
report with the remainder of the systems. The subreport is based on a
report query called Bart W1. This query has the following SQL:
SELECT *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Winter Week])=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="W") AND (([System Information].[Winter Week])=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Winter Week])=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Winter Week])=7)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="W") AND (([System Information].[Winter Week])=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="W") AND (([System Information].[Winter Week])=7));

So my question is do I add the coding to group here or do I need to create a
whole new query????

THANK YOU SOOOO MUCH FOR TAKING TIME TO HELP ME FIGURE THIS OUT!!!!! I
REALLLLLY APPRECIATE IT!



KARL DEWEY said:
Could you not use a Rank by Grouping query as I posted on 17 March?

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.

Then use the report Sorting and Grouping = Rank\20 and force new page on
new group.

Tia said:
I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
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.
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.
Click to expand...
Click to expand...
 
I couldn't get it to work
Ok, what did you do and what did noyt hapeen or did happen that was wrong
when you did it?
Did you do the Ranking in a Group query?

Tia said:
I wasn't clear on how to do this so I'm not sure that I did it right, but I
couldn't get it to work....Is there somewhere that I can learn more about
this?? I think it might be my best shot but I'm thinking that I'm not
entering it correctly so its not working...



KARL DEWEY said:
Could you not use a Rank by Grouping query as I posted on 17 March?

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.

Then use the report Sorting and Grouping = Rank\20 and force new page on
new group.

Tia said:
I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
TIA

On Tue, 17 Mar 2009 13:06:02 -0700, Tia wrote:

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.

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.
Click to expand...
Click to expand...
Click to expand...
 
I get compile error: Syntax error when I enter the code into the report. If
I enter it into the query, I get an error message. I'm not sure if it is
because my report and subreport are based on a query already so it won't let
me add to the query code (or I just don't know how to doit:))...I've been
trying to research group queries to see if there is something that is similar
to what you suggested to refer to, but I haven't had much luck.

Basically my layout is this: My report has the following; Customer_ID,
Customer_Address, This is linked to my subreport which has the followign:
Customer_ID, Customer_Address, System_ID, System_Type. The report and
subeport are linked through Customer_ID and Customer_Address. I then have a
query/filter that limits only Customer_ID based on Service_Tech and Service.
So when I select the report, I have one customer per page with the systems at
their location . My problem arises when there are more than 20 systems per
Customer per location. In that case, I need to have a second page that
includes the Custoemr_ID and Customer_Address.

So in order to use your query: Do I need to add a field to my existing
query?? Or start a whole new query and apply that to the report/subreport??

THANK YOU SO MUCH FOR YOUR TIME!! I REALLY APPRECIATE IT!!

KARL DEWEY said:
Ok, what did you do and what did noyt hapeen or did happen that was wrong
when you did it?
Did you do the Ranking in a Group query?

Tia said:
I wasn't clear on how to do this so I'm not sure that I did it right, but I
couldn't get it to work....Is there somewhere that I can learn more about
this?? I think it might be my best shot but I'm thinking that I'm not
entering it correctly so its not working...



KARL DEWEY said:
Could you not use a Rank by Grouping query as I posted on 17 March?

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.

Then use the report Sorting and Grouping = Rank\20 and force new page on
new group.

:

I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
TIA

On Tue, 17 Mar 2009 13:06:02 -0700, Tia wrote:

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.

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.
Click to expand...
Click to expand...
Click to expand...
 
First off, I think your query could be simplified to:
SELECT *
FROM [System Information]
WHERE Employee="Bart" AND Service IN ("A","W") AND [Winter Week] IN (0,1,7);

Second, if you want groups of 20 records, you must provide the sorting order
that would determine the first 20, second 20, ...

--
Duane Hookom
Microsoft Access MVP


Tia said:
Okay, I've been reading through all my access "bibles" trying to figure this
out...However, I'm getting more and more confused. So here's my layout.
I've got a report and subreport-they are linked by Customer ID and Customer
Address. The subreport lists all systems connected to the Customer ID. I
have a few Customers that have more than 20 systems so I need to create a new
report with the remainder of the systems. The subreport is based on a
report query called Bart W1. This query has the following SQL:
SELECT *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Winter Week])=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="W") AND (([System Information].[Winter Week])=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Winter Week])=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Winter Week])=7)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="W") AND (([System Information].[Winter Week])=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="W") AND (([System Information].[Winter Week])=7));

So my question is do I add the coding to group here or do I need to create a
whole new query????

THANK YOU SOOOO MUCH FOR TAKING TIME TO HELP ME FIGURE THIS OUT!!!!! I
REALLLLLY APPRECIATE IT!



KARL DEWEY said:
Could you not use a Rank by Grouping query as I posted on 17 March?

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.

Then use the report Sorting and Grouping = Rank\20 and force new page on
new group.

Tia said:
I did the following and am not getting the suggested result. When I put the
code in the report, nothing happens. I tried putting the code in the
subreport and then every system was seperated onto a seperate page but the
customer information only showed up on the first system of the group. Is
there something that I should check or that I may be missing?????

Thank you!!!
TIA

On Tue, 17 Mar 2009 13:06:02 -0700, Tia wrote:

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.

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top