Solved

Need to add totals from subreports in Detail section of report

Posted on 2009-03-29
31
552 Views
Last Modified: 2013-11-28
In the Detail section of a report, I have 4 subreports: Employees, Subcontractors, Materials, and Equipment. Each subreport has one total calculated in its report footer. In the Group Footer section, I need to add up all the totals for Employees, and add up all the totals for Subcontractors, and add up all the totals for Materials, etc.

Let's take just the Employees subreport as an example. On that subreport's report footer is a textbox named TotEmpl. So in the Group Footer section of the main report, I have a textbox with this calculation: =sum([Employee Hours and Cost].[Report]![TotEmp]), but it doesn't work, and I don't know why. When I try to run the report, it shows me a parameter box saying "Employee Hours and Cost.Report!TotEmp"

I sure hope someone can help me....
0
Comment
Question by:nbozzy
  • 17
  • 9
  • 4
31 Comments
 
LVL 3

Expert Comment

by:coleventures
Comment Utility
THIS IS NOT THE ONLY WAY to solve your problem.

This answer would work if you have your 4 subreports each driven by a query.

Your totals in the respective footers would be the result of totalling a column in the query.

Query1 has that field to be totalled called Q1value. (Employees)
Query2 has that field to be totalled called Q2value. (Subcontractors)
Query3 has that field to be totalled called Q3value. (Materials)
Query4 has that field to be totalled called Q4value. (Equipment)

I would use a UNION query to total these and then you'd be able to show them in you main report's footer.

Select Q1value as "ValueField" from Query1
UNION
Select Q2value from Query2
UNION
Select Q3value from Query3
UNION
Select Q4value from Query4;

Now you'd have one query with a field named "ValueField".
You could then total this field in your report.

Robert
0
 
LVL 3

Expert Comment

by:coleventures
Comment Utility
This link doesn't exactly apply to your problem but info found here has helped me immensely when trying to reference fields on forms/subforms, etc.

http://www.mvps.org/access/forms/frm0031.htm

Robert
0
 

Author Comment

by:nbozzy
Comment Utility
Robert, thank you very much for the link and the other detailed reply. My subreports ARE driven by queries, and the queries already have the total columns. But tell me how your UNION query would work; wouldn't it be adding the total for Employees plus the total for Subcontractors plus the total for Materials, etc? If so, that's not what I need (until the report footer!). In the Group Footer, I need the sum of all records' Employees, as well as the sum of all records' Subcontractors, etc. Does that make any sense?
0
 
LVL 3

Expert Comment

by:coleventures
Comment Utility
I am not clear on what you need.  Can you illustrate this somehow?
My answer was meant to total the one common field in each query giving you what you don't need.

Could you print an example of your report and mark it up?  Then you could scan it and post it here.  Or you could fax this info to my fax number found at www.robertgcole.com.

Robert
0
 

Author Comment

by:nbozzy
Comment Utility
Here's a sample database (change file extension to accdb after download). Open the report named "Invoice - Public Works" and when prompted to "Enter WOID" enter 6. Skip past the next prompt, which is caused by a non-working formula. Put the report into Print Preview.

For each Task in the Task Footer, I need all Employees totaled, all Subcontractors totaled, etc. Multiple Actions per Task will cause multiple Employees information, multiple Subcontractors information, etc. for that one Task.

When I have that, I will need the Report Footer to total Employees for all tasks, total Subcontractors for all tasks, etc. And then inder those, I will need a final total.

I hope this helps clarify, if clarification was needed. Thanks!!
forEEInvoice.txt
0
 

Author Comment

by:nbozzy
Comment Utility
Robert, I sent the above file before I saw your response asking for a report scan or fax. If you'd prefer a pic of the report only, please let me know and I'll post it. Thanks so much! ~~Nancy
0
 
LVL 3

Expert Comment

by:coleventures
Comment Utility
Can you print it out with your notations.  I'm more a visual person.

Robert
0
 

Author Comment

by:nbozzy
Comment Utility
Here it is in PDF.
Invoice---Public-Works.pdf
0
 

Author Comment

by:nbozzy
Comment Utility
If what I want to do is too difficult, I imagine that I could show a single subtotal for each Action, and again for each Task, with a grand total at the end.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
nbozzy,

The way in which you designed this report makes this issue difficult to troubleshoot.

For that reason, all I could do was to find the most direct solution.

Add the WorkOrders and WOTask tables to qryActionEmpCost:

SELECT WOTaskActions.ActionID, WOTaskActions.ActionTaken, WOTaskActions.ActionDate, Count(ActionEmployees.EmpID) AS [# of Employees], Sum(ActionEmployees.Hours) AS [Combined Hours], Sum([hours]*[hourlyrate]) AS [Cost of Action], WorkOrders.WOID
FROM WorkOrders INNER JOIN ((WOTasks INNER JOIN WOTaskActions ON WOTasks.WOTaskID = WOTaskActions.WOTaskID) INNER JOIN (Employees INNER JOIN ActionEmployees ON Employees.EmpID = ActionEmployees.EmpID) ON WOTaskActions.ActionID = ActionEmployees.ActionID) ON WorkOrders.WOID = WOTasks.WOID
GROUP BY WOTaskActions.ActionID, WOTaskActions.ActionTaken, WOTaskActions.ActionDate, WorkOrders.WOID;


This way you can use an aggregate Function in the Task Footer to sum the values based on the WOID.
  =DSum("[Cost of Action]","qryActionEmpCost","WOID=" & [WOID])

You must do the same thing for the other summaries.

Again there is probably a more elegant solution, but it might involve a re-design of your queries and/or Reports.

Other notes:
1. Please submit a sample with hardcoded values.
It is extremely frustrating and time consuming for us to fill in the "6" and bypass the "un-needed" parameter, *everytime* we regenerate the report.
(Sure it was easy enough to hardcode it myself, but now you have to "Un-hardcode it to test other scenarios)
So please submit a sample that allows us to efficiently see the problem and diagnose it.

2. Please compact you db before posting it.
Your 3.5 mb db compacted down to 1 mb (about 1 third of it's original size)

Thanks.

Sample attached

JeffCoachman
Access-EEQ-24275845SubReportTota.txt
0
 

Author Comment

by:nbozzy
Comment Utility
Hi, JeffCoachman:
Your solution works (thanks so much!), although I can't get a grand total in the Report Footer. I tried a textbox with a control source of:  =sum([txtTotEmpHrs]), which didn't work. How do I get a grand total for all Tasks?

Also, I hope you'll help me with the SQL for the other 3 subreports if I need it. I don't know SQL.

I create everything in Access using queries because I don't know SQL or VBA. Is this approach not right? And you mentioned that "the way in which you designed this report makes this issue difficult to troubleshoot." What did I do wrong? How should it be designed? I'd really like to learn to become better at this, and your insights here will be invaluable to me.

And thank you very much for the tips in the "Other notes" section. I will print them out so that I remember them. I have no desire to frustrate or alienate people who want to help me, so I appreciate your frank instructions.

0
 

Author Comment

by:nbozzy
Comment Utility
Sorry,
That formula was =sum([txtTotalEmpHrs]).
0
 

Author Comment

by:nbozzy
Comment Utility
Help? I tried to make the SQL modifications to the query for the Equipment subreport, and got this error: "You tried to execute a query that does not include the specified expression 'Make' as part of an aggregate function." The Help button was useless to me....code is attached (both before and after). I did add the two tables to the query grid before modifying the code.
Original SQL for Equipment subreport:

SELECT WOTaskActions.ActionID, WOTaskActions.ActionTaken, WOTaskActions.ActionDate, Equipment.Make, DateDiff("n",[startTime],[endtime])/60 AS [Action Time], ActionEquipment.HourlyRate, [hourlyrate]*[Action Time] AS [Equip Cost]

FROM WOTaskActions INNER JOIN (Equipment INNER JOIN ActionEquipment ON Equipment.EquipID = ActionEquipment.EquipID) ON WOTaskActions.ActionID = ActionEquipment.ActionID;
 

My Modified SQL for Equipment subreport:

SELECT WOTaskActions.ActionID, WOTaskActions.ActionTaken, WOTaskActions.ActionDate, Equipment.Make, DateDiff("n",[startTime],[endtime])/60 AS [Action Time], ActionEquipment.HourlyRate, [hourlyrate]*[Action Time] AS [Equip Cost], WorkOrders.WOID

FROM WorkOrders INNER JOIN ((WOTasks INNER JOIN WOTaskActions ON WOTasks.WOTaskID = WOTaskActions.WOTaskID) INNER JOIN (Equipment INNER JOIN ActionEquipment ON Equipment.EquipID = ActionEquipment.EquipID) ON WOTaskActions.ActionID = ActionEquipment.ActionID) ON WorkOrders.WOID = WOTasks.WOID

GROUP BY WOTaskActions.ActionID, WOTaskActions.ActionTaken, WOTaskActions.ActionDate, WorkOrders.WOID;

Open in new window

0
 

Author Comment

by:nbozzy
Comment Utility
JeffCoachman: I discovered after additional testing that the formula for the task footer is NOT giving the correct information. Under each Task, it is showing the total for all tasks. I think the formula needs to be changed to check that the TaskID matches, instead of the WOID? Not sure. I added the WOTaskID linking fields to the task header and the detail section, but I don't know how to re-write the DSum formula. What am I missing? Here's an updated copy. Thanks very much for helping!

P.S. I compacted and hard-coded this time!!!
ForEE-Invoice-2ndSend.txt
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Got errors because the subreports are missing
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:nbozzy
Comment Utility
Oh, SHOOT! I'm so sorry!
ForEE-Invoice-2ndSend.txt
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK, so tell me what each total should be and why.
Thanks
0
 

Author Comment

by:nbozzy
Comment Utility
First, let me say that if this beast can be completely rebuilt to make the calculations easier, I'm all for that. I want to learn to do this the right way, not just to fix my clumsy attempts, ok? Please teach me to be better!

As it is now, the first task (Catch Basin cleaning) has 2 actions (caught it & cleaned it). So the first task's footer should show "Subtotal of Employee Costs for this task:" as $532.00 (because Employee cost of first action = $255 and Employee cost of second action = $277.50).

The second task (plow snow) has 1 action (plowed). So that task's footer should show "Subtotal of Employee Costs for this task:" as $6.25.

Then in the report footer, I need Grand Total of Employee Costs of $538.25.

The same logic must also be applied to Subcontractor Costs, Equipment costs, and Materials costs, with a Final Grand Total of all in the Report footer.

Thank you so much for advising and helping! If this exceeds the scope of EE, please let me know and I will pay for consulting. I have no intention of abusing the nature of this website.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Well, if it were me I would not create a report this complex.

Perhaps a two or three simpler reports, with less groups would have conveyed the same information. (and been simpler to design and troubleshoot)

I'll look at this tonight

0
 

Author Comment

by:nbozzy
Comment Utility
Thanks, JeffCoachman -- any advice and solution will be immensely appreciated.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK,

Lets start from the top.

This whole issue could be avoided if you used the wizard to creata a "Grouped Report" instead of a main report with multiple subreports.

You would create a query that contains *Everything* you need in your report.
Then you would run the report wizard and select your groups, and their orders.

So you grouping order would something like this:
WOID
    Task
        Action

Then when you see the button for "Summary Options" you will simply select what summaries you need.

Here is a sample

JeffCoachman
Access-MultiTableGroupedReportWi.mdb
0
 

Author Comment

by:nbozzy
Comment Utility
JEffCoachman: I actually did use the report wizard and grouping for the first 3 levels, and then I brought Employees, Equipment, Subcontractors, and Materials in as subreports. I just tried again but the report wizard couldn't produce a report any more detailed than the first 3 levels, even though it allows 4. So I'm still stuck.

I don't know how to make this a less complicated report, because this is what they need to have on their invoice.

So what can I do?
0
 

Author Comment

by:nbozzy
Comment Utility
I found out that the DSum isn't working because DSum calculates values before data is grouped. So therefore, Sum should work. But every Sum fuction I attempt returns a parameter.

What is wrong with my syntax? All these return parameters from the Task Footer section -----
=Sum([Employee Hours and Cost].[Cost of Action])
=Sum([qryActionEmpCost].[Cost of Action])
=Sum([Employee Hours and Cost].[TotEmp])
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"I actually did use the report wizard and grouping for the first 3 levels"
That's odd, becuse you report is only grouped by 2 levels:
WOID
    WOTask.TaskID
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
nbozzy,

A few key issues.

1. Do not use Lookup fields in your tables, or reports (use then in your forms only)
See here for just a few reasons why:
http://www.mvps.org/access/lookupfields.htm

2. Do not use Captions in the table properties.
You had some captions like "Task" that were two totally different values in two totally different tables.
(Once the Report/Form is *working* then you can simply change the labels)

3. Please adopt a naming convention like LNC:
http://en.wikipedia.org/wiki/Leszynski_naming_convention
Or RVBA
http://www.xoc.net/standards/rvbanc.asp
(Prefix characters, no spaces in object Names, ...ect)

Without pulling any punches, it was a nightmare trying to decipher this design.

Other issues are that you have many TableNames, Field Names and Captions that are too similar:
Task
TaskName
Task Name
TaskNameID
WOTaskID
...ect
:-O

I managed to literally "Hack" this report to get the correct totals.

But I will say for the record that you should strongly consider applying all of the suggestions I listed above *BEFORE* proceeding any further with this report.

Again, my solution is a "Hack" to workaround all of the issues above.
AFAICT, there is no way you can modify this report in any way to get around this
It will be just as difficult for anyone else to troubleshoot this report in order to modify it.

Normally I dont do Hacks, or spend this much time on one question.
For that reason creating the EmpCost Totals is as far as I am prepared to go on this.
You will have to study what I have done and apply it to the other totals. (after addressing the issues above)


Yes you are correct in that the wizard will only create three grouping levels.
(you can add additional groups manually, using the Group and Sort Option)
However I really only see the need to group 2 levels anyway:
WOID
    Task

What you are using as subreports now, will simply be fields in the Detail section.
Then the totaling will be a breeze.
         
The key is creating a query containing *EVERY* Field you need.
Then verify that the query is accurate, and then use the Report Wizard to create a Report from it.
But again, you should really address all of the issues I listed above *FIRST*.

Lastly I strongly advise you to seek out the services of a Database Professional, to help you with this project.
There are very few experts here willing to troubleshoot a design like what you have here.

In conclusion I will say that it is possible that I missed something simple.
But years of doing this has taught me to start at the top with the raw tables and work my way down to the summaries in reports.
A "Hack" was not my intended solution.
So I stand buy my belief that you really should address the issues I listed above.

Sample with EmpCost Totals attached.

JeffCoachman


 
Access-EEQ-24275845ReportSubRepo.txt
0
 

Author Comment

by:nbozzy
Comment Utility
Oh you're right! (Been so long I've forgotten, I guess.) So I just ran the wizard with 3 grouping levels, and I must still be doing something wrong: I put a subreport in the detail section under the Action group. Is that right? The data looks right, but I still can't get a calculation to work in the Action footer. How do I have the Action footer show the sum of each detail's subreport? I can't believe I could be the first person to want to do this.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Again,
If create the "Mega-Query" there should be no need for subreports

Create the Mega Query

When you start the wizard, the Defalult grouping should be the same as the order in which your tables are related.

Again, look at the sample of the grouped report I posted.
That report has totals in each group, and there are no subreports needed.

Always keep things simple at first.

As a test just create a grouped report with:
WOID
    Task
        EmpInfo

...and try to get the totals.

Keep me posted
0
 

Author Comment

by:nbozzy
Comment Utility
Wow, JeffCoachman. That did it. Phew. A million thanks.

I honestly had no idea that captions or lookups at the table level were bad things, and I'm very grateful that you told me so I don't use either again. All the books I've read about Access say that putting captions and lookups at the table level make the creation of forms (& reports in the sense of the captions) easier. However, I've never had a database this complex, either.

Mea culpa on the naming conventions - I am guilty of using the standard only partially and swear that from today forward I will adhere completely.

Do you know of any books that would help me? I do have a book on SQL that I am definitely going to read and study (SQL Queries for Mere Mortals, Hernandez & Viescas). I want to improve and progress in my knowledge & abilities with Access.

Would you be willing to consult with me on the next database I will build (not this one; I'm sorry to cause you so much anguish here and I appreciate you taking it to a solution)? Specifically: comment/correct my table design before any other objects are built, and then any incidental help with queries/reports I may need after the table design is complete? Please email me at PLEASEMESPAM@SPAM.COM either way.

"Thank you" and 500 pts isn't near enough, but it's all I can do. I really appreciate all your time and advice.


ADMIN EDIT TO REMOVE PERSONAL EMAIL ADDRESS
0
 

Author Closing Comment

by:nbozzy
Comment Utility
JeffCoachman deserves a medal for this one, and I'm not kidding!!!!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
nbozzy,

Caption and look ups are not inherently bad, but as the link I posted states they obscure the real workings of the tables.
This hampers troubleshooting.  Where I was seeing text was in some cases really a number.
My theory is to leave the tables as raw as possible.


Captions are similar; I became confused when I saw two fields with the exact same name that contained different values.
I had to go back through the report to the query, then through the query to the table to get the real name and value.

Don't get me wrong, I am guilty of this sometimes as well.
Sometimes the name you think is fitting today will end up being unfitting a week form now.
The name you pick then will be inadequate a month from then.

The rule with naming conventions is not so much which one you use or even if you create your own.  There is no naming convention that is perfect in all aspects.
Some people like prefixes, some like suffixes.
Some people use underscores, others do not.
Some people use "Camel-Case" others us all caps.
The important thing is that you have one, and you use it consistently.
One thing almost everyone agrees on is to avoid using spaces in any object names.
There are great Find and replace utilities available:
http://www.rickworld.com/
Again, to be fair, I have my own naming convention based loosely on LNC.

Books?, I have Database Design for Mere Mortals.
IMHO, no book really teaches you better than experience. You have to know how to build solid tables before you worry about creating queries.

When I was starting out, I lived and breathed by the Northwind sample database
(the 1997-2003 version, NOT the 2007 version)
http://www.microsoft.com/downloads/details.aspx?familyid=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en


Any other contact we have should remain totally outside of this forum.
My email is in my profile.

I have asked that your email be removed from your post for your own security.

;-)

Jeff
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now