Getting totals from nested subforms

I have a subform called SubActionEmployees. Its footer has a textbox called txtActionEmp that is calculating and working properly. SubActionEmployees is on another subform called SubActions, and the default view for SubActions is single form. Its footer has a textbox called txtEmp that is pulling the data from txtActionEmp and its working properly UNTIL the next step&&..
SubActions is on another subform called SubWOTasks. When I open SubWOTasks, the footer for SubActions only shows the txtEmp value for the visible record, not for the total of all related records. For example, I view the two records in SubActions that are related to the visible record in SubWOTasks, and as I view each of SubActions two records, the SubActions footer shows the txtEmp value for that visible record only, where I need it to show the sum of txtEmp for both records. Control source for txtEmp is: =[SubActionEmployees]![txtActionEmp].

Thinking that I might need a DSum calculation in txtEmp, I tried about a million variations and never got past #Name?. If its a DSum that will do the job, can someone please give me the calculation? If not DSum, then why is this not working properly? Thanks so much! I've attached a picture.

PictureSubWOTaskCalcProblem.pdf
nbozzyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
nbozzy,

Then use a formula similar to what I demonstrated on the Report.

Basically it is a DSum

DSum (TheField,TheTableOrQuery, TheConditions)

Something like this (I'm just guessung here, Remember this is an example):

DSum ("Expenses", "ExpenseQuery", "EmployeeID=" & me.employeeID & " AND TaskID=" & me.taskID")
This would sum the ExpensesField in the ExpenseQuery, (or whatever table/query displays the Expenses), only for the current employee on the form and the current task on the form.

I am sure you get the idea.

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
nbozzy,

I thought the report we worked on displayed this data?
Why not just print the Report, and be done?

Forms are not meant to display totals in the way you are requesting.
Form records cannot be Grouped in the same way as reports.


From what I can tell, what you need is a form that is one level up from your data so that it will display each action for that specfic task.
Then it should be easy to get your sum for the total in the same way you are getting the sum for each employee.

Yes, you could use Dlookup and get any total you want, and insert it anywhere you want, but IMHO it would be very confusing to have a grand total on each form record, as you are requesting.
Besides, being that this is a form, which is "Graphically" more complex than a Report, the use of aggregate function will slow it down.

Let's see if other Experts have any other thoughts...

JeffCoachman
0
 
nbozzyAuthor Commented:
JeffCoachman: Hi! Yes, the report displays the data. However, I'd like the form to calculate it as well so staff can know with one look what the total was, and I would have it only visible if they had already produced the invoice report that you "saved" for me. I knew that form footers could store calculations and be referenced in a main form, so I didn't realize my desire was beyond the scope of forms.

Curious: why do you think it would be confusing to have the total on the work order's main form if it had been invoiced?

>>From what I can tell, what you need is a form that is one level up from your data so that it will display each action for that specfic task.<<  I'm not sure what you mean here, but I do have a main form for the entire work order which is what the invoice is for. The first subform is for tasks, second subform is for actions taken to complete the tasks, and third subforms are cost details for each action. So I already do have a higher-level form, but I don't know how this "makes it easy to get the sum" as you mentioned, because I thought I had to start at the lowest form and work backwords, gathering calculations as I go. What's the easy way??
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jeffrey CoachmanMIS LiasonCommented:
What I wat trying to say is if you wanted a Total, then that would go on the next level up, for examle something like this:
Year 2009
    Salseperson1
        Sale1, 1000
        Sale2, 2000
        Sale3, 3000
    SalesPerson1 Total, 6000
    Salseperson2
        Sale1, 1100
        Sale2, 1200
        Sale3, 1300
    SalesPerson2 Total, 3600

On the other hand, (from what I can see from your PDF File), you want something like this:

Year 2009
    Salseperson1
        Sale1, 1000
            Total, 6000
        Sale2, 2000
            Total, 6000
        Sale3, 3000
            Total, 6000
    SalesPerson1 Total, 6000
    Salseperson2
        Sale1, 1100
            Total, 3600
        Sale2, 1200
            Total, 3600
        Sale3, 1300
            Total, 3600
    SalesPerson2 Total, 3600

Again, anything can be done.
My issue is that there are just so many grouping levels and detail levels displayed in the Reoprt and in the Forms, that I get lost just trying to see what  summary goes with what group.

JeffCoachman
0
 
nbozzyAuthor Commented:
I don't care how it's calculated; I just want the total of all expenses from completing the work order. I figured if I got a solution to the original question, I could do the rest.
0
 
nbozzyAuthor Commented:
Sorry JeffCoachman - I've been buried in work and just got back here to check for a reply. I'll check this out over the weekend and reply back. Appreciate the help and patience.
0
 
Jeffrey CoachmanMIS LiasonCommented:
"I've been buried in work \"
;-)

I know the feeling...

;-)

Jeff
0
 
nbozzyAuthor Commented:
Jeff, truly sorry about the delay.

I can't get even basic calculations to work in a subform footer! Here are two situations; one works, and one doesn't, and I'm beating my head senseless.....

Situation 1 (works): subform has field named [HourlyRate]. Subform's footer has a textbox with this calculation:  =Sum([HourlyRate]). Works great.

Situation 2 (doesn't work): another subform has a calculated field in the detail section named txtCalcEmp, and it's control source is multiplying two fields, also in the detail section. Good. Subform's footer has a textbox with this calculation:   =Sum([txtCalcEmp]). Why won't this work? I know it worked in Access 2003 -- is something different in 2007?
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
You cannot Sum a Textbox, you can only Sum a field.

This is the way it has been since at least Access 2000.
So I don't see how summing a textbox could have worked for you in Access 2003.

Jeff
0
 
nbozzyAuthor Commented:
Jeff, this one took me a bit to figure out. I realize now what you mean. I don't even need to tell you that you're right (!). I looked again at the 2003 db and see that the working sum function is adding up something that was calcculated in the underlying query and presented as a field (am I correct that this worked because the query passed the calculated data to the form as a "field" and then I summed the "field?")

Ok, I think I'm armed with enough understanding now... I'll just wait for your answer to my confirmation question above to be sure I fully understand the distinction. Thanks SO much!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Confirmed!
;-)
0
 
nbozzyAuthor Commented:
Thanks so much for your time, Jeff. I also appreciate your helping me to understand.
0
 
Jeffrey CoachmanMIS LiasonCommented:
nbozzy,

Congratulations!

The goal of the site is to get you the answers you need.
The theory behind the site has always been that Experts are in an "Advisory" role.
(Not really a: "I have a question, just give me an answer", position)

In that vein, there is alway a "Learning" component.

My feeling was that since I had helped you with something similar in a prevoius question, that you could apply that knowledge to the current task at hand.

   "I also appreciate your helping me to understand."
There were many pople who helped an upstart Access Developer named Jeffrey Coachman to learn this stuff years ago too.

;-)

Jeff
0
 
nbozzyAuthor Commented:
Yes, Jeff, I much prefer to understand the underlying concepts rather than just be fed an answer. If you don't understand the "why" then you can't apply the information in other ways at other times.

Thanks again for your patience with me.
0
All Courses

From novice to tech pro — start learning today.