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

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.





0
Utf
4/7/2010 4:47:01 PM
access.reports 4434 articles. 0 followers. Follow

0 Replies
810 Views

Similar Articles

[PageSpeed] 19

Reply:

Similar Artilces:

Windows 7 Ultimate Office 2003 Valid Key Reported Invalid
I have just bought a new Dell system with Windows 7 Ultimate installed. I have a full install CD of Office 2003. When I try to install it I get an invalid key message even though the key is correct. I have re-verified the key through using Belarc on my old computer, and I have tried copying the files to disk and installing from there. There is no doubt the key is valid. Obviously, there is something system-wise that is causing this not to work. Is this an OEM or Home Use copy of Office 2003? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the d...

bound control or inbound control
Hi Katherine, There are differences between using bound versus unbound controls. Where on the form do you want to use an unbound control? Jeanette Cunningham "Katherine" <Katherine@discussions.microsoft.com> wrote in message news:23BBC192-0A74-4B9F-9513-5E5923A76658@microsoft.com... > ...

Use the Windows task scheduler to run a report
I need to use the Windows task scheduler to run a report, I can see how to do this if the access file is closed. What if the file is currently open ? regards Trevor -- Message posted via http://www.accessmonster.com please do not post the same message in multiple newsgroups at different times, thank you this is posted and answered in: microsoft.public.access.gettingstarted Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.com/user/LearnAccessByCrystal Access Basics http://www.AccessMVP.com/st...

why won't word 2007 release control of a .rtf file after exiting w
I AM HAVING TROUBLE WITH ACCESS TO RICH TEXT FILES BY PROGRAMS OTHER THAN WORD------ AFTER THE FILE OPEN IN WORD IS CLOSED AND EVEN AFTER WORD HAS BEEN SHUT DOWN (EXITED) ---------- I GET THE ERROR MESSAGE THAT THE FILE IS "IN USE" BY WORD WHEN (FOR EXAMPLE) AN ATTEMPT IS MADE TO DELETE IT ---- SOMETIMES WHEN RE-OPENING THE FILE AFTER RE-STARTING WORD THE FILE CANNOT BE SAVED BECAUSE IT IS IN "READ ONLY" MODE ------- AFTER MUCH FIDDLING AROUND I FINALLY MANAGED TO DELETE THE PROBLEM FILE ONLY TO HAVE THE SAME PROBLEM RETURN WITH A DIFFERNT FILE ----- WORD 200...

RE: Control
Can some one point me right direction to achive this following. Need to dispaly a data in a table format. some of the column data may have to display a image/icon based on a value from the table (1 :- Red color icon, 2:-yellow color icon...) Once the data is loaded and displayed on the format , able to search particular row by column index and update the values including the images(change different image). I tried with Listview control, with the report view I am not able to search row and update the columns with the List vew I am not able to show the image/icon. do not want to use ...

MSCRM login and the Users Control Panel
Is anyone aware of any way that the settings stored under Control Panel - Users - Password Management might interfere with the process of Single Sign On into MSCRM? It seemed to have somehow cached someones login and prevented logging in as the logged in user, until cached credentials had been "deleted". However I have never seen this mentioned as being relevant. I am not too sure what the architecture model is there, but it appears to be a single domain. If this area has any relevance, it would be useful to know....thanks ...

PUBLISHER
Please use this space to ask you question. And make sure you give us all of the information you can, especially which version of Publisher you are using. -- JoAnn Paules MVP Microsoft [Publisher] "Murrayjones" <Murrayjones@discussions.microsoft.com> wrote in message news:05C80DBE-0FF2-455E-93A9-5EE35E2F8D36@microsoft.com... > I am using Publisher 2000 and trying to insert numbering into a document with 10 tickets. Can you help? "JoAnn Paules [MSFT MVP]" wrote: > Please use this space to ask you question. And make sure you give us all of > ...

Spam control - MS Outlook
Hi! I am using MS Outlook 2000 and use 2 profiles. I recently installed a firewall package (Fsecure = Telia S�ker Surf), including Spam control. After install, MSO hangs and I cannot change to the other profile (if I not removed MSO by ctrl/alt/del) Somebody want to comment this? /Sven-Erik Intergrated AV/Spam is the cause of frequent issues in Outlook Is the FSecure you installed compatible with this old version of Outlook? "Storfille" <storfille@ebrev.net> wrote in message news:b1Ixm.11987$U5.164765@newsb.telia.net... > Hi! > > I am using MS Outlook 200...

numbering macro
In a database of say 10 columns and 100 rows, how do I record a macro that will o Insert a new column to the left of column A, then o in the new column A, number the rows that contain data in column B. Note: The numbering should start from 1. Since row 1 has the database heading & row 2 has the column headings, the numbering (no. 1) should start from row 3 and continue down to the last row that has data in column B. Columns("A:A").Insert For i = 1 to 100 If Cells(i,"B").Value <> "" Then Cells(i,"A&q...

Releasing memory after creating OCX control
Hi, I try to create an instance of an activex control in my MFC dialog based app. Though my application works with out any error, if i check the memory after calling "delete pMyControl" it keeps on occupying the same memory it occupied when the control was alive. Could anyone help me where I am missing to clear the memory? Thank you. My Code here ---------------m_pxWmp= new CWMPPlayer4();if( m_pxWmp->Create("replay", WS_CHILD | WS_BORDER | WS_VISIBLE , CRect(100, 100,320,240), this, 2)){ m_replay = true; m_pxWmp->SetUrl("C:\\video.avi"); m_pxWmp->Set...

Associate an activity to each record of a marketing list
Hi all, If Im not wrong, when distibuting a campaign activity, that activity is assigned to people (record owers or myself). However, I would like the activity to be displayed in the activities of each record of the marketing list. How to do that? Thank you! ...

Adding a control variable
Hello all, Under VC++ 7.1, I've added a checkbox to a dialog. Using the dialog editor, I am not trying to add a control variable for this check box. However, when I right-click on the check box and select "Add Variable", the resulting wizard dialog does not allow me to select "Control Variable" (it is grayed out). What do I need to do to get a control variable for this check box added to my class? Thanks, Dave I've never seen that before - are you able to duplicate this problem with other projects too? -- Regards, Nish [VC++ MVP] "Dave" <bett...

Access 2007: Record update timestamps
Hi all, I've been fighting with this one and can't find any information on it. I have a feeling it was easier to do this with Access 2003, but maybe not. If anyone can help me out, I'd appreciate it. In Access 2007, I have a form where if I edit any (or specific) fields I want it to stamp the current date and time in a separate field. So, for example: I have a "Notes" field. If I edit this field, I want it to stamp the time in a "DateModified" field at the bottom of the form. I have tried to use onDirty, AfterUpdate, BeforeUpdate with expressions as well as V...

xcel Number Formatting Problem
Hi there, I have exported accounting information from a company data website int excel. For some reason, where profit figures had a 1000 seperator (, on the website, when exported into excel the 1000 seperator became decimal point (.). Also, the profit figures taken from the website ar in GDP th i.e. excluding the last '000'. Therefore, please can you help me do the following: 1) Replace the unwanted decimal point (.) back to a 1000 seperato (,). 2) Add 000 to the end of the profit figures so that they are in writte in full. Many thanks And -- andyp16 ------------------------...

Transation sequence number issue
Happy new year everybody!! I have a customer with this situation. his invoice must be sequentially he can't skip a transaction number for his invoices, when a transaction is aborted a number is skiped. i konw that one is not hard to fix but this is the main issue when he do a return he need to have a differnt count numbers than the transactions number for example when he sale something the transation number 1545 is generated but is he return or do a store credit the transaction number is 145 without affectin the secuence os the sale transaction number. can be done? the other probl...

Getting the number of processors
What's the best way to get the number of processors? I was thinking of just getting it from the environment variable. -- Thanks. "Saucer Man" <saucerman@nospam.net> wrote in message news:uLnKelwiKHA.1572@TK2MSFTNGP04.phx.gbl... > What's the best way to get the number of processors? I was thinking of > just getting it from the environment variable. If WMI is an option then this may be a start: Sub main() MsgBox "Count processors=" & CountProcessors & vbCrLf & _ "Query processors=" & QueryProcessor...

stock control system
trying to see up a 'basic stock control sytem' for a warehouse that contains building materials. -- Thank you. You can use multiple ways of doing, there's 3 ways to come to m head... 1) Use a worksheet as database an save all the information on the cell of that worksheet using some macros to access it and handle. 2) You might want to use Access instead of Excel, I think would b easier to handle the database portion and you still have the Macro available. 3) You can use VB with an Access to create an application -- fanay ------------------------------------------------------...

what control can display characters from txt files in a dialog?
I want to read lines from txt files and display them on a dialog.I wonder which control can be put into the dialog to hold the characters from txt? Something like :- while(m_file.ReadString(tmpstr)) { str += tmpstr; } m_edit.SetWindowText(str); -- Regards, Nish [VC++ MVP] "liao_xf" <sclxf@sina.com> wrote in message news:001401c3652f$177b9830$a001280a@phx.gbl... > Thanks first! > I know that Edit control can receive and edit input. > and I know the filestdio can read lines from txt. > but can you tell me how to attatch the lines to the edit? > Thanks!...

Numbered List
New user of Outlook 2007 and recently created an email that had a numbered list that was quite long. It went to 17 items, and was left aligned. Is there anyway to change that to right aligned, short of using Word as the email editor. Thanks Carl well, you are using word as the editor in 2007.... Did you try selecting one of the items (or all) then clicking the little down arrow on the number list button and making a new numbered format? -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2...

Creating dialog controls at run-time
I've been researching how to do this, but whenever I try what other people do, I get very bad results. But my challenge doesn't end there. The number of controls I want to create is only known at run-time. The problems I face are manifold: 1. The code most people use fails badly. Not only is the font unconventionally big, but when I click the checkbox I create, the whole dialog disappears. If that's not enough, the pointer to the control is NULL when the dialog's destructor is called, so I can't delete the instance, and get a memory leak message when the program ends: CADi...

Another Customer Report question
I'm using RMS 2.0. Is there any way to pull a customer report that shows their shipping address? All I can find is a report that shows billing address. I'm just trying to get some basic marketing done and what to find out which customers are purchasing which products, and to what address I can send them information on new products. Why is this so difficult for me to do? With all the information being captured in RMS, you'd think simple reports such as these would be available. I looked through all my custom reports and partner source and unfortunately didn't find a...

MDI support in ATL composite control
Hi, I have components created in ATL. The components are ATL composite controls. Entire work has been done till now using ATL, however I now need to give MDI type of work area (based o doc-view architecture) within the control. I have class derived from CMDIFrameWnd. However when I try to initialize instance of this class by calling "Create" method of CFrameWnd (base class in CMDIFrameWnd), I get error and creation fails. Tracing the code, I found that base class create method calls "AfxGetInstanceHandle" to get handle to the DLL. Now since this is ATL Composite control, ...

Duplicate Records #2
I am not real proficient in Excel but received a large (18,000+) file from Data Processing in Excel to use for a mailing. My question is, I need to find duplicate addresses and delete those before I do the mailing. I work for the school district, so many families have more than one child in the district and they only need to receive one letter. Is there a way to do this? Thanks, Kristy -- Kristy ------------------------------------------------------------------------ Kristy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37546 View this thread: http://w...

X Report Printing
Here's my problem... I am unable to print a X, Z or ZZ report from my office printer. It will print to the receipt printer but I would like to be able to print it from the office on 8.5 x 11 paper. Does anyone have a template for this or know how I could go about setting this up so it will work. Any help would be most appreciated. Thanks! In SO Manager, Database/Registers/Receipt Formats, create a new format - call it something like "Full Page". Under "Receipt and Report templates", the last entry is labeled "Report" - click the magnifying glass to ...

View Filtering and Access Controls for Business Units
I am trying to implement MS-CRM for my company. We have multiple Business Units . I need to give views to each Business Units so that they see only their own Contacts/Accounts/Opp lists. How do I accomplish this ? Also, If I gave inter business unit acesses , how do I make sure that the one business unit users can not modify any fields on other's Business Unit's records ? Thanks in advance for any help, Kishore This is quite easy to do with CRM. Assuming you have already created your Business Units in CRM, you can view a security role within one of the Business Units and a...