subtotals/totals from SQL statement

Access 03/WinXP

I am using a function to generate an SQL statement based on several 
user-defined filters from a form.  This SQL statement is used as the record 
source in the OnOpen statement of the report property.  I have no problems 
with the SQL statement, filters, etc. - the report displays exactly the 
records requested.

Where I am having an issue in the the report's group subtotals and the 
report totals; the amounts calculated are the totals for the *entire* 
underlying table of the initial SQL statement from the function and *not* the 
filtered set of records.

I tried using the running totals options on the text boxes but the result 
was the same.

Suggestions are appreciated.
0
Utf
2/11/2008 7:23:03 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
740 Views

Similar Articles

[PageSpeed] 57

I am confused by this. I have never seen a report with totals that include 
more records than the report's record source. 

How are you calculating totals? Are you using something like:
  =Sum([FieldFromRecordSource])

-- 
Duane Hookom
Microsoft Access MVP


"Pendragon" wrote:

> Access 03/WinXP
> 
> I am using a function to generate an SQL statement based on several 
> user-defined filters from a form.  This SQL statement is used as the record 
> source in the OnOpen statement of the report property.  I have no problems 
> with the SQL statement, filters, etc. - the report displays exactly the 
> records requested.
> 
> Where I am having an issue in the the report's group subtotals and the 
> report totals; the amounts calculated are the totals for the *entire* 
> underlying table of the initial SQL statement from the function and *not* the 
> filtered set of records.
> 
> I tried using the running totals options on the text boxes but the result 
> was the same.
> 
> Suggestions are appreciated.
0
Utf
2/11/2008 8:36:17 PM
Sorry, should have included that.  Yes, I am.  The fields in the detail 
section are InvoiceAmount and PaidAmount; a third text box calculates the 
difference for a Balance Due.

The group footer and report footer simply utilizes Sum([InvoiceAmount]) and 
Sum([PaidAmount]).

"Duane Hookom" wrote:

> I am confused by this. I have never seen a report with totals that include 
> more records than the report's record source. 
> 
> How are you calculating totals? Are you using something like:
>   =Sum([FieldFromRecordSource])
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Pendragon" wrote:
> 
> > Access 03/WinXP
> > 
> > I am using a function to generate an SQL statement based on several 
> > user-defined filters from a form.  This SQL statement is used as the record 
> > source in the OnOpen statement of the report property.  I have no problems 
> > with the SQL statement, filters, etc. - the report displays exactly the 
> > records requested.
> > 
> > Where I am having an issue in the the report's group subtotals and the 
> > report totals; the amounts calculated are the totals for the *entire* 
> > underlying table of the initial SQL statement from the function and *not* the 
> > filtered set of records.
> > 
> > I tried using the running totals options on the text boxes but the result 
> > was the same.
> > 
> > Suggestions are appreciated.
0
Utf
2/11/2008 8:40:15 PM
The total/sum will not include values that are not in the report's record 
source query. 

Could you describe your method used to filter the report?

-- 
Duane Hookom
Microsoft Access MVP


"Pendragon" wrote:

> Sorry, should have included that.  Yes, I am.  The fields in the detail 
> section are InvoiceAmount and PaidAmount; a third text box calculates the 
> difference for a Balance Due.
> 
> The group footer and report footer simply utilizes Sum([InvoiceAmount]) and 
> Sum([PaidAmount]).
> 
> "Duane Hookom" wrote:
> 
> > I am confused by this. I have never seen a report with totals that include 
> > more records than the report's record source. 
> > 
> > How are you calculating totals? Are you using something like:
> >   =Sum([FieldFromRecordSource])
> > 
> > -- 
> > Duane Hookom
> > Microsoft Access MVP
> > 
> > 
> > "Pendragon" wrote:
> > 
> > > Access 03/WinXP
> > > 
> > > I am using a function to generate an SQL statement based on several 
> > > user-defined filters from a form.  This SQL statement is used as the record 
> > > source in the OnOpen statement of the report property.  I have no problems 
> > > with the SQL statement, filters, etc. - the report displays exactly the 
> > > records requested.
> > > 
> > > Where I am having an issue in the the report's group subtotals and the 
> > > report totals; the amounts calculated are the totals for the *entire* 
> > > underlying table of the initial SQL statement from the function and *not* the 
> > > filtered set of records.
> > > 
> > > I tried using the running totals options on the text boxes but the result 
> > > was the same.
> > > 
> > > Suggestions are appreciated.
0
Utf
2/11/2008 8:57:09 PM
Sorry to have taken your time - I resolved the issue. There was a table in 
the initial select statement (from a previous version of the report) which 
was creating a one-to-many situation below the level of the filters; thus the 
large sums.  When I removed the culprit, the report calculated correctly as 
you said it should.

ugh.

"Duane Hookom" wrote:

> The total/sum will not include values that are not in the report's record 
> source query. 
> 
> Could you describe your method used to filter the report?
> 
> -- 
> Duane Hookom
> Microsoft Access MVP
> 
> 
> "Pendragon" wrote:
> 
> > Sorry, should have included that.  Yes, I am.  The fields in the detail 
> > section are InvoiceAmount and PaidAmount; a third text box calculates the 
> > difference for a Balance Due.
> > 
> > The group footer and report footer simply utilizes Sum([InvoiceAmount]) and 
> > Sum([PaidAmount]).
> > 
> > "Duane Hookom" wrote:
> > 
> > > I am confused by this. I have never seen a report with totals that include 
> > > more records than the report's record source. 
> > > 
> > > How are you calculating totals? Are you using something like:
> > >   =Sum([FieldFromRecordSource])
> > > 
> > > -- 
> > > Duane Hookom
> > > Microsoft Access MVP
> > > 
> > > 
> > > "Pendragon" wrote:
> > > 
> > > > Access 03/WinXP
> > > > 
> > > > I am using a function to generate an SQL statement based on several 
> > > > user-defined filters from a form.  This SQL statement is used as the record 
> > > > source in the OnOpen statement of the report property.  I have no problems 
> > > > with the SQL statement, filters, etc. - the report displays exactly the 
> > > > records requested.
> > > > 
> > > > Where I am having an issue in the the report's group subtotals and the 
> > > > report totals; the amounts calculated are the totals for the *entire* 
> > > > underlying table of the initial SQL statement from the function and *not* the 
> > > > filtered set of records.
> > > > 
> > > > I tried using the running totals options on the text boxes but the result 
> > > > was the same.
> > > > 
> > > > Suggestions are appreciated.
0
Utf
2/11/2008 10:06:17 PM
Reply:

Similar Artilces:

sql 2008 data compression
We are thinking of running compression on our databases now that we moved over to SQL 2008 ? Our databases are quite huge and was wondering, how long does it take to compress ? I guess the answer is it depends.. but would be curious to get some examples of times. Also, is it an offline operation .. meaning the table(s) cant be accessed when the operation is running ? Thanks Raj (raj@hotmail.com) writes: > We are thinking of running compression on our databases now that we > moved over to SQL 2008 ? Our databases are quite huge and was wondering, > how long doe...

Cross tab query with duplicates and sum of total
In excel I have 3 columns. One is job number (there are some duplicates in here), One is Type(there are 4 categories; FNF, FNT,FNS,DNT), the last row is total months (this is a dollar total). I create a pivot off this; Type goes into page, job number goes into row, and SUM of total months goes into the data field. The SUM of total months takes the duplicates of job number and add the total. What I would like to do is create a cross tab query in access and I am wondering if access Sums the total months for job number the same way excel does? ...

Subtotals by page
Is there any way of getting excel to generate subtotals by page? these should update automatically when pagination changes. ...

Conditional Statement in Excel
Hi, I'm trying to create an if statement that whenever the user selects "Debit Memo" from the dropdown menu in column A (has other items-- Credit Memo Check, Transfer), the user would be forced to enter the corresponding value in column F (Debit). If the user attempts to enter the value in Column G (Credit)column, an error message would be generated "Incorrect Account". Grateful for any assistance in resolving this matter. WC One way: Select your cells in Column G (say, G2:G100, with G2 the active cell). Choose Data/Validation, and enter Allow: Custom Fo...

how can i prevent report totals from rounding
In the RMS reports, when you group your report items and the report shows subtotals and a grand total, the results are rounded, so that if you were to add the individual records on the report manually, the manual result is different than the displayed result because of rounding errors. can this be changed so that grouped totals are not rounded? thank you kevin can this be done?? thanks, kevin ...

Filter Subtotal
Is it possible to filter out a subtotal row? If I have a matrix as follows, is there a way to remove the Subtotal3 row/line? Total Labor 75.00 Overhead 50.00 Subtotal1 125.00 Training 50.00 Travel 50.00 Subtotal2 100.00 G&A 75.00 Subtotal3 75.00 <<---remove? Total All 300.00 Hopefully this came out right, sorry if it did not. Is there a way to attach an image? You can do the row visibiliy to false, You need to have an appropriate expression set he...

Subtotals?
On the Account List page I wish there were sub-totals under Favorite Accounts and Other Accounts. Anyone know of a setting or hack to do this? Future functionality? ...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...

Statement download problem
Hello, I'm using Money 2006 since it was released, and never had any problems. Suddenly two days ago I started having problems downloading and importing statements. I download or import statement with no errors, get message in Money that I have statement to read, but when I go to this account - there are no new transactions there. This happens to ALL accounts, not just one. Same happens when I just update Money directly. Tried to repair file, completely uninstall and reinstall Money - no luck. Do you know what could it be? I would appreciate your help. Thank you, -- Peter Afon...

Excel subtotal
I have a large file with a layout (main area only) as follows: Name Expense ............. Brown, John $100 Brown, John $150 Brown, John $200 Doe, Jane $110 Brown, Jane $130 Brown, Jane $210 .. .. .. I need to subtotal each person's expense (names may be different each period I do this)and copy/export the expense items and subtotals for each person to a separate worksheet to be emailed to them. There may be 30 names and 2000 lines of data. Is there a way to autonomate this process (rather than subtalling then copying each person's items to a new worksh...

Crystal Report for Account Statements - accountReceivableHistory,
Rather than using the built-in account statements, I'd like to do it in Crystal so that I can have full control. I'm hoping that someone else has done this work and can perhaps provide a Crystal report. Thanks for reading. Tim ...

Subtotaling a subtotaled spreadsheet
In excel before the 2003 edition I could subtotal a spreadsheet then subtotal again by a different column. It does not work now. All of the items are subtotaled but not in correct order. For example some of the totals calculate above the information not below. Please help! Hi Alane i use this feature extensively and have found no difference in its behaviour between any of the versions, however it is important that the data is sorted in the same categories and order that it is to be subtotalled, e.g. i have a worksheet that has region, state, department information on it and i want t...

RMS and SQL Express 2005 SP2
Does anyone tryed this combination to use RMS 1.3R whit SQL Express 2005 SP2. Have important bugs or security issues fixed?? thanks for your answers Roberto we are currently running it. no problems. "robertinho" <romanutd@hotmail.com.(donotspam)> wrote in message news:F98B9AA0-18F2-4537-9AAC-C0D072ADCFFD@microsoft.com... > Does anyone tryed this combination to use RMS 1.3R whit SQL Express 2005 > SP2. > Have important bugs or security issues fixed?? > thanks for your answers > Roberto ...

count the total number of certain values
I'm doing the net promotor score. How do I get the total number of cells with 9 and 10? Is there an example of calculating net promotor scores? Thanks, Hello there. I do believe you are wanting to try and work with the Countif function. There's plenty of good help on that in Excel and online. As the best advice I can / could & will give to anyone not entirely familiar with Excel, the best way to work out long and or complex problems is to break it down, one column (or row) at a time. IE: If you have all your data in a list format (and the 9's & 10's you are tryin...

IF Statement with Average Function results in #Value!
I wrote an IF Statement that takes the Average of a group of cells based on a certain condition. Here is the Formula: {=AVERAGE(IF(($A$30:$A$515=A1)*($H$30:$H$515=B1),$Q$30:$Q$515))} When a add a second part to the IF statement, I receive the error message #Value! I am able to add a number, but not text. Example: {=AVERAGE(IF(($A$30:$A$515=A1)*($H$30:$H$515=B1),$Q$30:$Q$515),"FALSE")} I want to add the "FALSE" LOGIC TO MY IF Statement. Ideas? Hi! >I want to add the "FALSE" LOGIC TO MY IF Statement. Well, for starters you have the "FALSE&...

how do I add a list of numbers to get a total
If your numbers are in cells A1 through A10, try: =SUM(A1:A10) If you have something more complex in mind, please post an example. tj "bibs" wrote: > Hi! If your list is in the range A1:A25: =SUM(A1:A25) Biff ...

Nesting IF statements, Conditional Formatting
Hi Folks, I'm hoping someone has an easy answer. I have a dillemma I wanted to address with conditional formatting but the conditional formatting only allows for 3 circumstances. I have a column of percentages - 0 through 100%. I have a series of ranks - A through F and X. A = >0 through 20% B = 21 through 40% C = 41 through 60% D = 61 through 80% F = 81 through 100% X = 0% If the percentages are in column I, and I want column J to reflect the proper letter rank, based on I, how do I nest my formula in J to show the right information, *or* how do I use conditional formatting? I in...

SQL to set a supplier
Can someone please help me with the sql to set all the items in a department to one specific supplier? Any help would be appreciated. Craig Craig, are those items currently assigned to any supplier? or they don't have any supplier assigned? -- Ahmed Nashat http://ahmed.nashat.googlepages.com "Craig" <craig@cctropicalparadisepets.com> wrote in message news:CC4E5CE2-36DF-4F28-AC20-A51696529F74@microsoft.com... > Can someone please help me with the sql to set all the items in a > department to one specific supplier? Any help would be appreciated. > Craig...

2007 OLAP Cube building against SQL 2005 Cluster
I've setup an OLAP cube before against a non-cluster SQL setup. I followed the technet guide (http://technet.microsoft.com/en-us/library/cc197552.aspx), installed the SQL prereq's on the Project Server, and added the SSP service account with full admin permissions on the Analysis instance. However, I'm getting an error: Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server corpsql03moss\moss. Error: Cannot connect to the repository. Analysis server: corpsql03moss\moss Er...

removing subtotals
Sometimes when I add subtotals and then remove them the sheet still has the subtotal 1,2 columns on the left side of the sheet. How can I remove them. gls858 First, make sure you select the entire range (or whole worksheet) that has the subtotals. In face, I'd select all the cells and try removing them once more. But in xl2003, you can hide those outlining symbols via: tools|options|view Tab|uncheck Outline Symbols. gls858 wrote: > > Sometimes when I add subtotals and then remove them the sheet still has > the subtotal 1,2 columns on the left side of the sheet. > How can...

Project resource hours subtotals
We have a program of linked projects that use an enterprise resource pool (Project and Server 2003). In Project's Resource Usage view, hours for all project work (not only these linked projects but any project that the resource has been assigned to) is totalled by resource. The resource shows 20,000 total hours but only has 50 hours of work on this particular plan. We are looking for a way to easily segregate these hours by project. We are currently accomplishing this by adding a Project column and then deleting any line item not associated with the project in question. This...

subtotals
Hi, I'm working with Excel 2003. When doing a subtotal, frequently I do not get the options to expand or contract the totals. These are the three boxes in the upper left hand part of the sheet. Sometimes I get them, sometimes I don't. Any ideas? thx/Jerry I've never seen xl fail to put those outlining symbols in, but maybe you could try this: Tools|Options|View Tab|make sure "Outline Symbols" is checked. Jerry wrote: > > Hi, I'm working with Excel 2003. When doing a subtotal, frequently I do not > get the options to expand or contract the totals...

Running Totals
Hi, everyody. I have prepard a worksheet that logs electicity meter readings. Against each reading I want the previous added to the current so I can view a running total of the electricity used for the year each week. Example: Column G shows the KWH used for that week. Column H shows the number of KWH used as a running total. This works okay with (H2)+(G3) H2 being the previous week's total. My problem is, when auto inserting the whole year's column the last entry shows all the way to the bottom of the worksheet. Is there a formula I can use so that the part of the column...

GP10 with Server & SQL 2008
The server is running perfectly. Trying to get client workstations connected and I am getting this error on examination of the testBCP file resaults in: [Microsoft][SQL Server Native Client 10.0]Connection is not enabled for BCP Derek, If your OS and SQL Server are 64-bit versions, take a look at the following KB article: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb$en-us$918983&wa=wsignin1.0 Specifically, you will want to check the ODBC configuration options. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglob...

Problem with 2 levels of subtotals
I have a weird situation here. I'm trying to create 2 levels of subtotals from the spreadsheet. I have no problem creating 2 levels of subtotals using Excel 2000. However, with Excel 2003 I keep on getting 'mix up' answers on the second level subtotal. The first level of subtotal works fine until I try to create the second level of subtotals. Can somebody please advice whether is there a way around this problem in Excel 2003? Thanks in advance I'm not sure what mixed up answers are, but there are a couple of things that I can think of that can go astray. #1. The ...