Limit records shown per report

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

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.
 
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.
 
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???



KARL DEWEY said:
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.


Tia said:
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.
Click to expand...
Click to expand...
 
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.


Tia said:
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???



KARL DEWEY said:
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.


Tia said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...

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!!!

KARL DEWEY said:
report???
I assume in the subreport as that will identify customer and location.

--
Build a little, test a little.


Tia said:
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???



KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Post the SQL of the query that feeds your subform and its name.

--
Build a little, test a little.


Tia said:
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
Click to expand...
Click to expand...

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!!!

KARL DEWEY said:
Would I place the code in the query for the subreport or the query for the
Click to expand...
report???
I assume in the subreport as that will identify customer and location.

--
Build a little, test a little.


Tia said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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;
 
Back
Top