Limit records shown per report

[Tia]

I use a report to show all systems located at a specific customer. When a
customer has more than 20 systems, I would like a new report to start with
all the same information in the header, etc. Any suggestions?? I'm using
Access 2007.

 

[KARL DEWEY]

If I understand you want to group 20 records per page with header repeated on
each page.

UNTESTED UNTESTED UNTESTED
In your query that feeds the report add field -- ZZZ: 1

Use Ranking in a Group in follow-on query with \20 to number the records.
((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Group on Group_20 and set Repeat Section to Yes with force page.

 

[Tia]

My report is set up with all the header information on the report and then
all the systems are listed on subreport. They are connected through Customer
ID. Would I place the code in the query for the subreport or the query for
the report???

If I understand you want to group 20 records per page with header repeated on
each page.

UNTESTED UNTESTED UNTESTED
In your query that feeds the report add field -- ZZZ: 1

Use Ranking in a Group in follow-on query with \20 to number the records.
((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Group on Group_20 and set Repeat Section to Yes with force page.

--
Build a little, test a little.

I use a report to show all systems located at a specific customer. When a
customer has more than 20 systems, I would like a new report to start with
all the same information in the header, etc. Any suggestions?? I'm using
Access 2007.

 

[KARL DEWEY]

Would I place the code in the query for the subreport or the query for the
report???
I assume in the subreport as that will identify customer and location.

--
Build a little, test a little.

My report is set up with all the header information on the report and then
all the systems are listed on subreport. They are connected through Customer
ID. Would I place the code in the query for the subreport or the query for
the report???

If I understand you want to group 20 records per page with header repeated on
each page.

UNTESTED UNTESTED UNTESTED
In your query that feeds the report add field -- ZZZ: 1

Use Ranking in a Group in follow-on query with \20 to number the records.
((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Group on Group_20 and set Repeat Section to Yes with force page.

--
Build a little, test a little.

I use a report to show all systems located at a specific customer. When a
customer has more than 20 systems, I would like a new report to start with
all the same information in the header, etc. Any suggestions?? I'm using
Access 2007.

 

[Tia]

Where do I enter the following code:

Use Ranking in a Group in follow-on query with \20 to number the records.

((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Is this in the query or the report? I'm not sure what is meant by
"follow-on" query.

Thank you so much for your time!!!

report???
I assume in the subreport as that will identify customer and location.

--
Build a little, test a little.

My report is set up with all the header information on the report and then
all the systems are listed on subreport. They are connected through Customer
ID. Would I place the code in the query for the subreport or the query for
the report???

If I understand you want to group 20 records per page with header repeated on
each page.

UNTESTED UNTESTED UNTESTED
In your query that feeds the report add field -- ZZZ: 1

Use Ranking in a Group in follow-on query with \20 to number the records.
((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Group on Group_20 and set Repeat Section to Yes with force page.

--
Build a little, test a little.


:

I use a report to show all systems located at a specific customer. When a
customer has more than 20 systems, I would like a new report to start with
all the same information in the header, etc. Any suggestions?? I'm using
Access 2007.

 

[KARL DEWEY]

Post the SQL of the query that feeds your subform and its name.

--
Build a little, test a little.

Where do I enter the following code:

Use Ranking in a Group in follow-on query with \20 to number the records.

((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Is this in the query or the report? I'm not sure what is meant by
"follow-on" query.

Thank you so much for your time!!!

Would I place the code in the query for the subreport or the query for the

report???
I assume in the subreport as that will identify customer and location.

--
Build a little, test a little.

My report is set up with all the header information on the report and then
all the systems are listed on subreport. They are connected through Customer
ID. Would I place the code in the query for the subreport or the query for
the report???



:

If I understand you want to group 20 records per page with header repeated on
each page.

UNTESTED UNTESTED UNTESTED
In your query that feeds the report add field -- ZZZ: 1

Use Ranking in a Group in follow-on query with \20 to number the records.
((SELECT Count(*) FROM YourQuery AS [XX] WHERE [XX].[ZZZ] =
YourQuery.[ZZZ]) \20) AS Group_20

Group on Group_20 and set Repeat Section to Yes with force page.

--
Build a little, test a little.


:

I use a report to show all systems located at a specific customer. When a
customer has more than 20 systems, I would like a new report to start with
all the same information in the header, etc. Any suggestions?? I'm using
Access 2007.

 

[Tia]

Query for report is named Bart S1 report:

SELECT [Customer Information].Customer_ID, [Customer Information].Customer,
[Bart S1].Service_Address, [Bart S1].Employee, [Service Address].Manager,
ServiceRequirements2.Type_of_System, ServiceRequirements2.Raw_Water,
ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles,
ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1,
ServiceRequirements2.Range_2, ServiceRequirements2.Range_3,
ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, 1 AS ZZZ
FROM ServiceRequirements2 INNER JOIN ([Service Address] INNER JOIN
([Customer Information] INNER JOIN [Bart S1] ON [Customer
Information].Customer_ID = [Bart S1].Customer_ID) ON [Service
Address].Service_Address = [Bart S1].Service_Address) ON
ServiceRequirements2.Type_of_System = [Bart S1].Type_of_System
GROUP BY [Customer Information].Customer_ID, [Customer
Information].Customer, [Bart S1].Service_Address, [Bart S1].Employee,
[Service Address].Manager, ServiceRequirements2.Type_of_System,
ServiceRequirements2.Raw_Water, ServiceRequirements2.Treated_Water,
ServiceRequirements2.Cycles, ServiceRequirements2.Inhibitor_Level,
ServiceRequirements2.Range_1, ServiceRequirements2.Range_2,
ServiceRequirements2.Range_3, ServiceRequirements2.Range_4,
ServiceRequirements2.Range_5, 1;