Control the number of records shown in a report/subreport.

T

Tia

I've posted this question before and when I follow the advice, I keep getting
a circular reference error. So I'm hoping that if I post some more info, I
may get insight as to what is going wrong.

I have a report (BartS1report) it uses a query (BartS1Report). This report
as a subreport (BartS1Sub2) that uses the query (BartS1). The report lists
the customer name, address, and systems serviced. The subreport lists the
systems serviced. The reports are connected through the Master/Child links
Customer ID and the ServiceAddress. When there are more than 20 systems per
customer, I would like to have a second report started for the same customer
with the remaining systems.

Here's the SQL statements:

BartS1 (query for subreport)
SELECT 1 AS ZZZ, *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Summer Week])=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Summer Week])=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].[Summer Week])=7)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="S") AND (([System Information].[Summer Week])=0)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="S") AND (([System Information].[Summer Week])=1)) OR
((([System Information].Employee)="Bart") AND (([System
Information].Service)="S") AND (([System Information].[Summer Week])=7));

BartS1Report (query for Main 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;


In the past the code, I was trying to use the following sql statement in the
BartS1Report query for the main report:

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;

Any suggestions or insight would be GREATLY appreciated!!! Thanks for your
time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Circular Reference Error 3
Limit Records Shown Per Report 5
Repost: Please Help 6
Control # of fields shown on subreport 1
Totaling the number of records on a report 4

Top