Avereging the Subtotals in Reports

I am trying to do this in the Reports. 
Lets say, in the details, there are different account numbers with 
corresponding balances.  These balances are totalled in the footer of that 
Group (month)  =Sum([balances]).  Then, I need to Average all the monthly 
totals in the footer of the Report.  In the report footer, if i use 
=Avg([balances]) , it averages ALL the details, NOT the monthly totals.
Any help?  thanks.

0
Utf
1/7/2010 9:08:02 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
1256 Views

Similar Articles

[PageSpeed] 40

I would create a totals query that can be added to the report's record source 
query. For instance, if you want to find the Average of the Monthly Freight 
values from the Orders table in Northwind, the SQL would be:

SELECT Avg(MthlyFreightAvg) as TheAvg
FROM 
(SELECT Format([OrderDate],"yyyymm") AS YrMth, Avg(Orders.Freight) AS 
MthlyFreightAvg
FROM Orders
GROUP BY Format([OrderDate],"yyyymm")) Q;

You can then save and add this query to your report's record source.

-- 
Duane Hookom
Microsoft Access MVP


"Ferdie" wrote:

> I am trying to do this in the Reports. 
> Lets say, in the details, there are different account numbers with 
> corresponding balances.  These balances are totalled in the footer of that 
> Group (month)  =Sum([balances]).  Then, I need to Average all the monthly 
> totals in the footer of the Report.  In the report footer, if i use 
> =Avg([balances]) , it averages ALL the details, NOT the monthly totals.
> Any help?  thanks.
> 
0
Utf
1/7/2010 9:17:02 PM
Reply:

Similar Artilces:

To MVP John Spencer
Hey! Sorry my bad English! My Name Is Per Erik Løkken, and comes from Norway. I have been in contact with MVP Duane Hookom regarding an issue with a report in Access 2007. She tells me that you've had a post regarding the ranking query: “I believe you can do this without the subquery but using a totals query and a self join. MVP John Spencer has posted this type of ranking query in the past. You will get a more efficient and functional result as needed in the report record source.” My report is made with a subquery: SELECT A.VEKT, A.NAVN, A.KLUBB, A.KLASSE, (S...

401K report
It would sure be nice if Great Plains had a report that shows totals for 401K benefits and deductions with payroll. It would also be nice to have totals for all columns. -- Phoenix USA, Inc. Systems and Programmer Analyst ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message p...

Reports & calender error
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C8EA66.66BA2C60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, When trying to run CRM reports, calender we got theseerror: "Reports.config has invalid schema, and could not be loaded" for report = and "Unexpected Error An error has occurred. For more information, contact your system = administrator." for calender. CRM 3.0, SQL 2005 SP1, W2K3 Standard Edition SP1 Please advice. Regards, Adan ------=_NextPart_000_0018_01C8EA66.66BA2C60 C...

graphic report
After using FRx to create Financial Statements, graphic report would be much better for presentation purpose, such as EBIT comparision between actual, budget and forecast. Does anyone give help on how to? Thanks in advance. With FRx 6.5 and below, you need to export to Excel and then use Excel's charting functionality. With FRx 6.7, you have the ability to export to an Excel Pivot Chart. You can also export to a spreadsheet and create a chart using Excel's charting functionality. "Pam" <Pam@discussions.microsoft.com> wrote in message news:4854660B-EC57-410E...

Reporting Errors?
I have a spreadsheet where there are descriptions (Net Sales, Purchases, Salaries, Bonus, etc.) in column A and the values are in column AT. On another tab, I want to be able to show which amounts have 0 as well as which have errors (div0, ref, etc.). I know I could just do something like... =IF(OR(ISERROR(AT7),AT7=0),A7,"Error") for each line, but I rather have it just show the ones that people need to check. The best would be... Purchases = 0 Bonus = Error Not... Net Sales = Purchases = 0 Salaries = Bonus = Error Any ideas? Some kind of lookup that can display a list? A mac...

Report for customer count
I need help to get last year customerS count report. How do I get it on RMS 1.3 I need this report by year. by weekly or monthly. Hi Gill, 1. You can accomplish this by modifying the existing .grp file: Add the below mentioned code into your "Customer - Customer List.qrp" file located at \Program Files\Microsoft Retail Management System\Store Operations\Reports\ Folder. 2. After you have done so. Go to Store Manager> Reports > Customer > List and generate your report. 3. Drag Year, Month and Week column to at the begining 4. Select the grouping you want. 5. Memorize...

Report Writer Page Two
Is there any way to create a page 2 of a report? I would like to be able to print purchase orders with all necessary info (which i already have created) but then i would like, at the same time, to print a second page to always print out that has all of the same info minus the prices. -- Thanks Knavas ...

Trial Balance Summary Report #2
My Trial Balance report is not balancing. At the end of the report's Ending Balance' totals; it has an amount which needed to be zero. How do I find accounts that are out of balance? Check out the automated solutions. There are two that will help you. https://mbs.microsoft.com/customersource/support/automatedsolutions8minus.htm "NJ" wrote: > My Trial Balance report is not balancing. At the end of the report's Ending > Balance' totals; it has an amount which needed to be zero. > How do I find accounts that are out of balance? Steve, I have already ru...

Modified Report Modified with VBA (Blocked)
Hi to all, I have a the message "Modified Report Modified with VBA (Blocked)" on Maintenance of customizations screen on each VBA modifed report. This does not allow me to Export the report. What should I do to remove the "Blocked" message? Regards This happens when you have references in your project and its lost. For example you can have ADO "reference, or RetrieveGlobals reference. You only have to reasign the reference and the "Blocked" message disapears. Regards, Takashi "Jeantex" <Jeantex@discussions.microsoft.com> escribi� en el...

How to add SY03900 table in the Sales Report?
Dynamics GP Sales Order line item descriptions are limited to 100 characters only. Our company needs more than that and we decided to enter it in the Item Notes field. We are not using the Line Item Notes field when we create quotations as we have other usage for that as well. My question is, how do I add and print the Item Notes field in the customized Quotation Form when I can't seem to add table SY03900 in the report itself? Please help! Hi Derf, Since you have mentioned that you want to show the Item Notes in Customized Quotation Form, I assume that you are working on a 3rd ...

Report Printing / Custom Paper Size
Hi All, I have a report that needs to be printed on a dot-matrix printer (and this is not the default printer). The area of one form, not including the tractor feed area, is 8.5" wide x 7" high. In the Access report designer, the report's size is: 8" wide x 6.5" high. For the Page Setup I have all of the margins set to .25" (for Top, Bottom, Left, Right). My questions are these: 1. For the report's Page Setup / Orientation, which is the correct format to use: "Portrait" or "Landscape"? 2. On the Page Setup / Columns tab, what shoul...

Formatting currency within a report text box expression
I'm trying to create a mail merge letter from a 2007 ACCB database using a report (Word can't cope with the filter) To keep the text flowing to allow for expansion of various field values, I've put the following in a text box. ="According to my records you " & [Expr2] & " " & [KEYNO] & ", have made a total payment of " & [PAID] & " comprising a permit fee of " & [PERMIT£] & " and key deposit of " & [KEY£] & ". Therefore " & [Expr1] Ignoring Expr1 &...

adjusting the height of text boxes in a report
I'm creating a report that is required to be in a specific format which has a text box in the detail followed by a subreport directly to the right of the text box. The subreport will grow depending on the amount of data. What I want is the text box adjacent to it to be sized on the report equal to the subreport. What I've tried writing is: Dim height as interger height = reports.item("subreport").windowheight me.textbox.height = height but I get the error stating that subreport cannot be found. Is this the right code or what am I doing wrong. Thanks Dan Reports...

Exchange Manager reporting Status of server as 'In Maintenance Mode'
Hi, I am running Exchange 2003 SP1 on a SBS 2003 server. My problem is that the server has stopped receiving email, I can send emails out no problem and the POP3 connector works fine. I am also running the SMTP connector for other accounts and this has stopped working. There is nothing I can see in the Event logs, the only 'abnormal' thing I can see is that the system manager is reporting the servers status as 'In maintenance mode' What does this mean and would this stop me from receiving email via the SMTP Connector? Thanks in advance Richard I'm no SBS/POP connect...

Merging Reports
I hope someone can help me out... I'm looking to create a single report that will essentially merge the results from the following RMS reports: Items - Inventory Movement Report Items - Item Quantity List I want to see items that have had movement (either received or sold) during the past season OR are still on hand from previous seasons. Currently I have to generate two separate reports, export them to excel, and then manually sort them and remove duplicate information. Thanks in advance to anyone who can help! Jeff, I appreciate the help that is exactly what I am looking for! ...

Expression in Access "Reports"
I have a field in my table called [Sale Type]. There are 3 different choices for this field: "Retail", "Wholesale" or "Warranty Only". I'm trying to build an expression in my report that counts the number of records in that field, but excludes "Warranty Only" entries. I'm using =count([Sale Type] = "Retail","Wholesale"). I get an error saying "The expression you entered has a function containing the wrong number of arguments". Access 2007. Try: =-Sum([Sale Type] <> "Warranty Only&qu...

Daily sales report
Is it possible to run/create a daily sales report which will give me total sales by day over a set period of time? Thanks. coupleofdogs have you tried to run the crystal report named reanaly ?? That should do it. -- Elizabeth M. "coupleofdogs" <anonymous@discussions.microsoft.com> wrote in message news:889001c47846$804054c0$a501280a@phx.gbl... > Is it possible to run/create a daily sales report which > will give me total sales by day over a set period of time? > > Thanks. > coupleofdogs ...

Building Columns in a Report
I'm using AC2003. I have a database containing inventory numbers. Is there a way to generate a report with 3 or 4 columns on one page of those inventory numbers? for example 123 456 789 012 345 678 901 234 567 890 123 456 789 012 345 678 Any help is appreciated! Peter, there is a way in the page set up part of formatting a report - to have several columns. It is quite a while since I have needed to do something like this, so I can't be any more specific than that. Jeanette Cunningham "PeterM" <PeterM@discussions.microsoft....

Need Report to Show Items that have no associated Supplier
We just ran a Top Suppliers report. Much to our surprise, our third top Supplier was blank. No Name. This No-Name supplier managed to account for 263 items, so we still have a few holes to plug. We have THOUSANDS of SKUS, so a manual search is out of the question. Is there a report out there that will do this? Thanks Bud run this query ****************** SELECT Itemlookupcode,Description FROM Item LEFT JOIN Supplier ON Item.SupplierID=Supplier.ID where Supplier.ID is null ******************* "Bud Izen" <BudIzen@discussions.microsoft.com> wrote in message news:8C1B3442-...

How can I run a GP sales report by State
I need to run a sales report in Great Plains showing all of our sales by state, one state in particular. Is there an existing report, or do I have to create one in Smartlist? If Smartlist is the right option, where should I start? Thank you, Adam You might be able to modify the sales report in Report Writer to sort by state and then add state header and footer sections to the report. You didn't mention which report you were trying to run or what type of information you need. If you're not a Report Writer person, I would go with SmartList or some external report writer. -- C...

Top Sales days top z report days report
Does anyone know how to find my top sales dates/ days with highest sales /highest z reports ? Does any one know how to find those in RMS? Is there a custom report for this ? Thanks Robert Robert: Do you want to look at your past journals/Z reports and print a new report in descending order by sales showing dates? Over what period of time: last month, quarter, year...? Aloha, Miles miles@pacificcomp.biz Pacific Computer Systems Miles Baidack Supporting Business Since 1985 Phone: (808)395-8156 or (808)277-8156 "Robert" wrote: > Does anyone know how to find my top sale...

E-mail parts of 1 Reports to different users
I am not a coder and not familiar with VBA but I'm hoping osmeone can help me with my problem: I have a 600 page report grouped by ID number and I would like to know how to program this one report into creating a pdf file or snapshot and auto e-mail to each of the ID owner. I have the adobe writer so I can print the report to adobe, I'm just not sure how to make the report print in multiple files based on ID number. I would really appreaciate all the help. ...

Problem with subtotal function
I've noticed since changing to Excel 2003 that when I have a spreadsheet that I want to do multiple subtotals on that if I have one grouping within a group followed by multiple groupings within a group that my subtotals start aligning in such a way that it is very confusing to read. Has anyone run across this and know of any fix? For example, I have sorted a spreadsheet with names, locations & other data by names, then location then subtotaled by name then by location. My results are Location 1 subtotal Location 2 subtotal Name 1 subtotal Location 1subtotal Locati...

Errors publishing reports
Hi all, has anyone come across this error when trying to publish a custom report onto the CRM server? "Unable to retrieve cluster members. Transport Error: Communication Failure" This occurs when you 'Save As' a report > Enterprise > the usual administrator name and blank password > Click Ok It will freeze for like a minute before popping up the error. This had worked before and now wonder why it isn't working now. I have re build the Crystal APS database and now am totally lost on what to do. If anyone has come across this problem, please help me! Cheers ...

Add day of week to data from external report (html)
I have a spreadsheet which lists a date and time as 1/02/2007 8:31:36 AM. I would like to be able to add the day of the week for the date in column A. It would be preferred to have the day appear directly in the column but would be acceptable to add a column for it. -- Gerry St As long as you have that date and time in a cell as an Excel datetime then it is simply a matter of formatting in Excel, Format (menu), cells, custom ddd mm/dd/yyyy hh:mm -- --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: ...