Limit Records Shown Per Report

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

Tia

Question

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.


Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 8:21 AM PST



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.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/3/2010 8:39 AM PST



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



:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 11:40 AM PST


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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/3/2010 1:57 PM PST



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

:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 3:06 PM PST



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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/9/2010 10:20 AM PST



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;
 
This has one short coming as the first page will only have 19 records.
You will need to group on Record_Count and force a new page.

SELECT [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5,
((SELECT Sum([XX].[ZZZ]) FROM [Bart S1 report] AS [XX] WHERE [Bart S1
report].Customer_ID & [Bart S1 report].Customer & [Bart S1
report].Service_Address & [Bart S1 report].Employee & [Bart S1
report].Manager & [Bart S1 report].Type_of_System & [Bart S1
report].Raw_Water & [Bart S1 report].Treated_Water & [Bart S1 report].Cycles
& [Bart S1 report].Inhibitor_Level & [Bart S1 report].Range_1 & [Bart S1
report].Range_2 & [Bart S1 report].Range_3 & [Bart S1 report].Range_4 & [Bart
S1 report].Range_5 >= [XX].Customer_ID & [XX].Customer &
[XX].Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 &
[XX].Range_5)\20)+1 AS Record_Count
FROM [Bart S1 report]
ORDER BY [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5;

--
Build a little, test a little.


Tia said:
Question

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.


Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 8:21 AM PST



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.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/3/2010 8:39 AM PST



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



:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 11:40 AM PST


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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/3/2010 1:57 PM PST



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

:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 3:06 PM PST



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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.

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

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
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
Click to expand...
Click to expand...
Click to expand...
 
I'm getting the following error message:
Syntax error in query expression'((SELECT Sum([XX].[ZZZ]) FROM
[BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID &
[BartS1Report].Customer & [BartS1Report].Service_Address &
[BartS1Report].Employee & [BartS1Report].Manager &
[BartS1Report].Type_of_System & [BartS1Report].Raw_Water &
[BartS1Report].Treated_Water & [BartS1Report].Cycles &
[BartS1Report].Inhibitor_Level & [BartS1Report].Range_1 &
[BartS1Report].Range_2 & [BartS1Report].Range_3 & [BartS1Report].Range_4 &
[BartS1Report].Range_5.=[XX].Cusotmer_ID & [XX].Customer &
[XX[.Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 & [XX].Range_5)



KARL DEWEY said:
This has one short coming as the first page will only have 19 records.
You will need to group on Record_Count and force a new page.

SELECT [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5,
((SELECT Sum([XX].[ZZZ]) FROM [Bart S1 report] AS [XX] WHERE [Bart S1
report].Customer_ID & [Bart S1 report].Customer & [Bart S1
report].Service_Address & [Bart S1 report].Employee & [Bart S1
report].Manager & [Bart S1 report].Type_of_System & [Bart S1
report].Raw_Water & [Bart S1 report].Treated_Water & [Bart S1 report].Cycles
& [Bart S1 report].Inhibitor_Level & [Bart S1 report].Range_1 & [Bart S1
report].Range_2 & [Bart S1 report].Range_3 & [Bart S1 report].Range_4 & [Bart
S1 report].Range_5 >= [XX].Customer_ID & [XX].Customer &
[XX].Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 &
[XX].Range_5)\20)+1 AS Record_Count
FROM [Bart S1 report]
ORDER BY [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5;

--
Build a little, test a little.


Tia said:
Question

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.


Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 8:21 AM PST



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.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/3/2010 8:39 AM PST



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



:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 11:40 AM PST


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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





Tia 2/3/2010 1:57 PM PST



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

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

:

Click to show or hide original message or reply text.

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



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 3:06 PM PST



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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.

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.
Click to expand...
Click to expand...
Click to expand...
 
I reentered the code and the error went away....

However, when I open the report from my switchboard, I get a circular
reference error??? What do I need to fix or check for?

Thank you!

Tia said:
I'm getting the following error message:
Syntax error in query expression'((SELECT Sum([XX].[ZZZ]) FROM
[BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID &
[BartS1Report].Customer & [BartS1Report].Service_Address &
[BartS1Report].Employee & [BartS1Report].Manager &
[BartS1Report].Type_of_System & [BartS1Report].Raw_Water &
[BartS1Report].Treated_Water & [BartS1Report].Cycles &
[BartS1Report].Inhibitor_Level & [BartS1Report].Range_1 &
[BartS1Report].Range_2 & [BartS1Report].Range_3 & [BartS1Report].Range_4 &
[BartS1Report].Range_5.=[XX].Cusotmer_ID & [XX].Customer &
[XX[.Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 & [XX].Range_5)



KARL DEWEY said:
This has one short coming as the first page will only have 19 records.
You will need to group on Record_Count and force a new page.

SELECT [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5,
((SELECT Sum([XX].[ZZZ]) FROM [Bart S1 report] AS [XX] WHERE [Bart S1
report].Customer_ID & [Bart S1 report].Customer & [Bart S1
report].Service_Address & [Bart S1 report].Employee & [Bart S1
report].Manager & [Bart S1 report].Type_of_System & [Bart S1
report].Raw_Water & [Bart S1 report].Treated_Water & [Bart S1 report].Cycles
& [Bart S1 report].Inhibitor_Level & [Bart S1 report].Range_1 & [Bart S1
report].Range_2 & [Bart S1 report].Range_3 & [Bart S1 report].Range_4 & [Bart
S1 report].Range_5 >= [XX].Customer_ID & [XX].Customer &
[XX].Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 &
[XX].Range_5)\20)+1 AS Record_Count
FROM [Bart S1 report]
ORDER BY [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5;

--
Build a little, test a little.


Tia said:
Question

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.


Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 8:21 AM PST



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.


:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





Tia 2/3/2010 8:39 AM PST



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



:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 11:40 AM PST



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.


:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





Tia 2/3/2010 1:57 PM PST



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

:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 3:06 PM PST



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

--
Build a little, test a little.


:

Click to show or hide original message or reply text.
Click to expand...
Click to expand...
Click to expand...
 
Code associated with button on form that opens BartS1Report.

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "BartS1Report", acViewPreview, acViewNormal
DoCmd.OutputTo acReport, "BartS1Report", "RichTextFormat(*.rtf)",
"Service Report.rtf", False, "", 0


Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

Tia said:
I reentered the code and the error went away....

However, when I open the report from my switchboard, I get a circular
reference error??? What do I need to fix or check for?

Thank you!

Tia said:
I'm getting the following error message:
Syntax error in query expression'((SELECT Sum([XX].[ZZZ]) FROM
[BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID &
[BartS1Report].Customer & [BartS1Report].Service_Address &
[BartS1Report].Employee & [BartS1Report].Manager &
[BartS1Report].Type_of_System & [BartS1Report].Raw_Water &
[BartS1Report].Treated_Water & [BartS1Report].Cycles &
[BartS1Report].Inhibitor_Level & [BartS1Report].Range_1 &
[BartS1Report].Range_2 & [BartS1Report].Range_3 & [BartS1Report].Range_4 &
[BartS1Report].Range_5.=[XX].Cusotmer_ID & [XX].Customer &
[XX[.Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 & [XX].Range_5)



KARL DEWEY said:
This has one short coming as the first page will only have 19 records.
You will need to group on Record_Count and force a new page.

SELECT [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5,
((SELECT Sum([XX].[ZZZ]) FROM [Bart S1 report] AS [XX] WHERE [Bart S1
report].Customer_ID & [Bart S1 report].Customer & [Bart S1
report].Service_Address & [Bart S1 report].Employee & [Bart S1
report].Manager & [Bart S1 report].Type_of_System & [Bart S1
report].Raw_Water & [Bart S1 report].Treated_Water & [Bart S1 report].Cycles
& [Bart S1 report].Inhibitor_Level & [Bart S1 report].Range_1 & [Bart S1
report].Range_2 & [Bart S1 report].Range_3 & [Bart S1 report].Range_4 & [Bart
S1 report].Range_5 >= [XX].Customer_ID & [XX].Customer &
[XX].Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 &
[XX].Range_5)\20)+1 AS Record_Count
FROM [Bart S1 report]
ORDER BY [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5;

--
Build a little, test a little.


:

Question

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.


Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 8:21 AM PST



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.


:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





Tia 2/3/2010 8:39 AM PST



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



:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 11:40 AM PST



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.


:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





Tia 2/3/2010 1:57 PM PST



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

:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 3:06 PM PST



Post the SQL of the query that feeds your subform and its name.
Click to expand...
Click to expand...
Click to expand...
 
I do not know VBA.

A circular reference usually is from something like this --
zzzz, Sum([Field_Y]) AS [Field_Y], aaa,
where you use same output name as input.

--
Build a little, test a little.


Tia said:
Code associated with button on form that opens BartS1Report.

Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "BartS1Report", acViewPreview, acViewNormal
DoCmd.OutputTo acReport, "BartS1Report", "RichTextFormat(*.rtf)",
"Service Report.rtf", False, "", 0


Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

Tia said:
I reentered the code and the error went away....

However, when I open the report from my switchboard, I get a circular
reference error??? What do I need to fix or check for?

Thank you!

Tia said:
I'm getting the following error message:
Syntax error in query expression'((SELECT Sum([XX].[ZZZ]) FROM
[BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID &
[BartS1Report].Customer & [BartS1Report].Service_Address &
[BartS1Report].Employee & [BartS1Report].Manager &
[BartS1Report].Type_of_System & [BartS1Report].Raw_Water &
[BartS1Report].Treated_Water & [BartS1Report].Cycles &
[BartS1Report].Inhibitor_Level & [BartS1Report].Range_1 &
[BartS1Report].Range_2 & [BartS1Report].Range_3 & [BartS1Report].Range_4 &
[BartS1Report].Range_5.=[XX].Cusotmer_ID & [XX].Customer &
[XX[.Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 & [XX].Range_5)



:

This has one short coming as the first page will only have 19 records.
You will need to group on Record_Count and force a new page.

SELECT [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5,
((SELECT Sum([XX].[ZZZ]) FROM [Bart S1 report] AS [XX] WHERE [Bart S1
report].Customer_ID & [Bart S1 report].Customer & [Bart S1
report].Service_Address & [Bart S1 report].Employee & [Bart S1
report].Manager & [Bart S1 report].Type_of_System & [Bart S1
report].Raw_Water & [Bart S1 report].Treated_Water & [Bart S1 report].Cycles
& [Bart S1 report].Inhibitor_Level & [Bart S1 report].Range_1 & [Bart S1
report].Range_2 & [Bart S1 report].Range_3 & [Bart S1 report].Range_4 & [Bart
S1 report].Range_5 >= [XX].Customer_ID & [XX].Customer &
[XX].Service_Address & [XX].Employee & [XX].Manager & [XX].Type_of_System &
[XX].Raw_Water & [XX].Treated_Water & [XX].Cycles & [XX].Inhibitor_Level &
[XX].Range_1 & [XX].Range_2 & [XX].Range_3 & [XX].Range_4 &
[XX].Range_5)\20)+1 AS Record_Count
FROM [Bart S1 report]
ORDER BY [Bart S1 report].Customer_ID, [Bart S1 report].Customer, [Bart S1
report].Service_Address, [Bart S1 report].Employee, [Bart S1 report].Manager,
[Bart S1 report].Type_of_System, [Bart S1 report].Raw_Water, [Bart S1
report].Treated_Water, [Bart S1 report].Cycles, [Bart S1
report].Inhibitor_Level, [Bart S1 report].Range_1, [Bart S1 report].Range_2,
[Bart S1 report].Range_3, [Bart S1 report].Range_4, [Bart S1 report].Range_5;

--
Build a little, test a little.


:

Question

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.


Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 8:21 AM PST



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.


:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





Tia 2/3/2010 8:39 AM PST



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



:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





KARL DEWEY 2/3/2010 11:40 AM PST



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.


:

Click to show or hide original message or reply text.


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.



Was this post helpful to you?
Reply Top





Tia 2/3/2010 1:57 PM PST



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

:

Click to show or hide original message or reply text.


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.

--
Click to expand...
Click to expand...
Click to expand...
 
Back
Top