Solved

Getting totals from nested subforms

Posted on 2009-04-04
14
498 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

19 Experts available now in Live!

Get 1:1 Help Now