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
1111 Views

Similar Articles

[PageSpeed] 7

Reply:

Similar Artilces:

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

incorrect check / cheque number printing
When generating a cheque run, let’s say cheque 61, 62, 63 and 64, the information printed on cheque # 62 is actually cheque # 64 and vice versa. This does not happen every cheque run, but it does happen fairly often. I have not seen GP do this. What version and service pack of GP are you using? Are you 100% sure the check stock itself is not out of sequence? Do you have any customizations? -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Syed" <Syed@discussions.microsoft.com> wrote in message news:3B02ECEF-7436-4CF3-930E-AA4D2DFDF117@microsoft.com... ...

Trouble with Tab Control
I have a form in which i have a tab on it. The problem is the tab appears white and I would like to show the background behind it. I saw in the properties there was a section to make it "transparent" or "normal" i've selected botha nd nothing changes. Any ideas on how to solve this. thanks On Jan 25, 9:32 am, tsla...@gmail.com wrote: > I have a form in which i have a tab on it. The problem is the tab > appears white and I would like to show the background behind it. I > saw in the properties there was a section to make it "transparent" or > ...

Transferring control of CClientDC to CDC
Hi, I have a class MSWinDisplayManager which I want to take a CClientDC device context so that it's member functions can perform drawing routines on it. I want the class to have it's own CClientDC member which all the methods have access to draw on. My constructor looks like this: MSWinDisplayManager::MSWinDisplayManager(CClientDC& win) { private_win.attach(win); } This is called by the user like: CClientDC dlg(this); MSWinDisplayManager wdm(dlg); then I want to do things like: wdm.drawCars(); The problem I have is that private_win isn't getting control of the device ...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Roman and Arabic numbers in SEQ
Running 2003 on XP. Got a doc that has SEQ numbering. The code is: ARTICLE{seq level0\h\r0}{seq level1\h\r0} etc. to level 7 and the last entry is {seq level0\r1\*ROMAN) This displays ARTICLE I The next level down is has this code: {seq level0\c\*ROMAN}.{seq level1\r1 \*arabic} This displays as : I.1, with numbers until article five displaying as V.1, V.2, etc. I want it to display as 1.1, 1.2, etc. I tried replacing the ROMAN with arabic, but it didn't change anything. I have spent hours trying to figure this out. Using reveal formating and styles, they are no styles...

Invoice Numbers 10-27-07
We produce reports that are invoices.. The reports are really a group of compined reports if this matters... When we print the reports I would like to have printed consecutive invoice numbers. If possible I would like to have the number apprear as AS-00001, AS-00002 ect.. I am not really interested in storing the invoice numbers I just need them on the printed invoice as it is made of of groups of various data that is stored... Thank In Advance for you help. Bob If you just want a consequetive numbering on the report, all with an AS- prefix, see: Numbering Entries in a Report o...

Input to custom reports
Hello, I'd like to be able to input values to a custom report like the Year. Is there any way to package a custom report and provide input to it? Every year the user has to go into the custom report and change the Year restriction. Alternatively, (this is a payroll report), is there any global variable or some other back door that I can use to get the current year in a custom report? Given the report writer may not be the ticket what would the recommendation be to create custom reports that require input? VB.NET? -- Russ Using VBA you can allow for input in the custom reports. B...

running reports from MS CRM prompt for username and password
Senario: When running a report from Microsoft CRM client it prompts for username and password. When given it's runs the report correct question: if MS CRM 3 is configured correctly is it normal that when trying to generate a report in MS CRM prompt for username and password? No, ideally it should pass the users AD credentials to the reporting IIS server (single sign on). If you were to close the browser and run a different report does it still prompt for credentials? I've noticed this does also depend on the hostname. For instance if the reporting server uses a differe...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etc… Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

How do I bind a XAML text box control to a dataset?
Hello; I am new to using WinFx and I am having trouble figuring out how to bind a text box to a field in a dataset. I found an MSDN article: http://msdn2.microsoft.com/en-us/library/ms752057.aspx My question deals with the text box code: <TextBlock Text="{Binding Path=ISBN}" Grid.Column="1" /> How does this text box know to bind to the field "ISBN" in "myDataSet"? How does the control implement the dataset? Thank you Monty ...

Controlling NDR's sent to Originator
Hi, I have a scenario: An Exchange2k User sets up an Auto Forward Rule to forward all inbound mail to a personal email account. This person recieves email into his Inbox and the Rule fires off forwarding the email to their personal account. His personal account is not reachable and Exchange sends a NDR to the original sender. Example: Step 1: john23@example.com sends email to corpuser7@corp14.com Step 2: corpuser7@corp14.com has an Inbox rule that forwards this email to his personal account at homeacct@isporg.com Step 3: exchangeserver.corp14.com recieves an NDR from pubmailer.isporg.c...

Report to show Item Class Distribution Amounts
We would like to create a report, using Crystal Reports, that would show the following: dollar amount break down of the Sales Distribution accounts (COGS and Sales) per item class based on a date range. What is the most accurate way of going about this? We could only think of this method: (in short) sum the Ext Price based on SOP30300.CSLSINDX and SLSINDX and hope it matches the SOP10102 summed distribution amounts. Any advice would be appreciated. Thanks in advance. With the SLSINDX you would use the Extended Price and the CSLSINDX you would use Extended Cost. You would probably ...

isinteg reports configuring tstmgr failed : ecBadVersion
we have lost our mail after NAV did it's worst. have performed eseutil /p and /d as suggested elsewhere now get error from isinteg help please Update - have now sorted this thanks ...

me too, problem with import... only 15 records
me too, problem with import... only 15 records.... also in one of my customers crm i need a fast answer.... I dont see a question to answer...? JUAN MARKETING wrote: > me too, problem with import... only 15 records.... also in one of my > customers crm > > i need a fast answer.... Hehe yeah, what is the question!? what are you trying to import? Sorry, I don't know the reason, bu each time i try to import x number of accounts or contacts, MSCRM only import 15 records... Promise, everything is ok... columns, format csv from excel, size, records.... I say that, becau...

Picture control on a form
What is the best way to fill a form with a picture control so that when the client window is resized the picture control will fill the form? -- Just Al Take a look at this and see if this is what you want: http://www.codeproject.com/dialog/bmpdlg01.asp Tom "Al" <Al@discussions.microsoft.com> wrote in message news:5BEA0FA2-3F95-4090-B908-A75DBF1D19DE@microsoft.com... > What is the best way to fill a form with a picture control so that when > the > client window is resized the picture control will fill the form? > -- > Just Al Thank You! -- Just Al &...

Auto calc YTD Numbers
Cells A1:L1 equal "Jan" thru "Dec". M1 equals "YTD". Cells A2:L2 equal numeric values. If you haven't guessed it already, I would like M2 to sum all the values A2:L2 for those months previous and including the current month (but not future months). What is the best way for going about this Here are 2 ways =SUM(OFFSET($A$2,,,,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0))) or =SUM(OFFSET($A$2,,,,MONTH(TODAY()))) hopefully your computer's clock is accurate -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjo...

Subquery
Hi, I'm trying to code a subquery on a table but to no avail...it's a very simple table that has Date (short date, just recording the day) and NAV which is a number (double). All I'm wanting to do is have a query that displays the Date, the NAV and the NAV from the day before - only recording business dates. In other words, the subquery number I want is the NAV from the record preceeding the current one. I've got as far as the below, but that just gets the same NAV number as today's: SELECT tblNAV.Date, tblNAV.NAV, (SELECT LAST(x.NAV) FROM tblNAV as X LEFT JOIN tblNAV on...

Sum a group of numbers
When I first started using Excel, if I had a bunch of different totals on a worksheet, I could hold down the right mouse button and cover the group I wanted to add. The sum of these numbers would appear on the bottom of either the worksheet or a task bar. I have lost the ability to do this. Or I have lost the task bar. How do I get it back? Hi Barbie, That would be the status bar. If it is not showing, go to View>Status Bar to turn it on. If the sum function is still not showing, right click anywhere on the status bar and select which option you want. HTH Martin "Barbie...

Crystal Report Launch 2
(sorry to repost this thread, but this was too old and I got no respones!) Can anyone explain this better? I can't find any documentation about this.........and techknowledge seems to be useless: http://www.greatplains.com/TechKnowledge/techknowledge.asp?print=true&id=28987&code=7842 can anyone show an example? I tried looking at the url of a report (almost a mile long!) and I indeed saw the paramters I put, but when I try to reopen that url in another browser I get this error: Unable to connect: incorrect log on parameters. File 7c433cc1d949d16.rpt. any help would be greatly ...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...