Solved

Getting totals from nested subforms

Posted on 2009-04-04
14
512 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:nbozzy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24069489
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
 

Author Comment

by:nbozzy
ID: 24071940
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24072335
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nbozzy
ID: 24072375
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24072561
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
 

Author Comment

by:nbozzy
ID: 24106445
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24106909
"I've been buried in work \"
;-)

I know the feeling...

;-)

Jeff
0
 

Author Comment

by:nbozzy
ID: 24207809
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 24211714
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
 

Author Comment

by:nbozzy
ID: 24227278
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24229826
Confirmed!
;-)
0
 

Author Closing Comment

by:nbozzy
ID: 31566616
Thanks so much for your time, Jeff. I also appreciate your helping me to understand.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24237621
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
 

Author Comment

by:nbozzy
ID: 24237681
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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