Summing Continuous Form and Multiple Forms (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
Question1:
I have a form with a couple of subforms.
The main form is per project and has a ProjectID primary key.
Both subforms are continuous forms and linked via the ProjectID to the main/parent form.
One of fields in the subforms I would like to sum and store it but it has to be group by the ProjectID.
Let's call them SubformTotal1 and SubformTotal2. Both are textboxes in located in their respective subforms.
If I use an aggregate query, my main form is no longer updatable. Which causes a problem.

Question 2:
Same set up as above but now I want to add both SubformTotal1 and SubformTotal2 and store it.
This new textbox called GrandTotal would be on the main form.

How can I do this?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
If both subforms are linked by project id then in each subform footer just sum the field; =Sum([someField]). That will group by project id since you are linking the subform.

However, there is usually no reason to ever store calculated values. Just calculate when you want to display.

Without seeing the setup, I would like use a dsum in my main form for the grand total that passes in the project id. Can you provide a sample db?
 

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
The reason to store the values is to create charts that the boss desperately wants!
If I do not have the values in a table or query, I'm not sure how to create a chart.
So let's get specific.
The boss wants a chart to show Cat Total score per project.
He also wants a chart to show Grand Total score per project.
So, something similar to the chart I created for Project Cost per project, shown below.
This is from the same database.
1609887074795.png

So for the attached database, you need to open it, select an FY and then click the 'Project Score Sheet' button.
This is the form I am referring to below.
1609887225166.png


This opens the 'ProjectScoreSheet_F' as shown below.
I'm looking to store the values of 'Cat Total' and 'Grand Total'.
I'll add the second subform, namely 'Additives_F' later but it will be very similar to the subform 'Catergory_F'.
Then, the Grand Total will be calculated from Cat Total + Additive Total.

So how can I do this?

1609887377330.png
 

Attachments

  • MIRAC ver10_FE - 4.zip
    263.6 KB · Views: 150

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
The reason to store the values is to create charts that the boss desperately wants!
I will take a look, but bottom line if you can calculate it once in a form you sure can do it whenever you want in a query, so no need to store it.
That is equivalent to typing calculated values in a spreadsheet instead of having a formula to do the calculation. It is so easy for calculated values to go "stale" when you update some other values.
 

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
Thanks for looking.
I should add again that I want to keep the form updatable.
I managed to get the data needed but then my form was no longer updatable, which is not good of course.
 

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
With your dsum suggestion, I'm a step closer. See below.
Added the Cat_Total to the ps_Category_Q that feeds the Catergory_F.
Is this a good way to accomplish it?

Then on to the next step..... I'll keep plugging unless you have an idea.
1609888863926.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
No I would not put that in the main form query.

I would base my charts off simple aggregate queries.
The cat total score per project and grand total scores would be simple aggregate queries, no need to store anything.

qryCatScoreByProject

ps_ProjectIDSUM_Cat_Score
1​
75​
2​
55​
3​
55​
4​
75​
5​
50​
6​
10​
7​
75​
8​
38.75​
9​
55​
10​
38.75​
I noticed your additives do not have a foreign key to a project?

You can then make a simple function to show on the main form
Code:
Public Function GetCatScore(ProjectID As Variant) As Double
  If Not IsNull(ProjectID) Then
    GetCatScore = DLookup("Sum_Cat_Score", "qryCatScoreByProject", "ps_ProjectID = " & ProjectID)
  End If
 End Function

Do the same for additives.
Then you can use these two on the form grand total. This would be different than the source of the charts which could be all sql.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
I am going to assume you are adding a foreign key to the additives. In that case you do an aggregate on categories by projectID with a sum as shown. Then you do a groupby project id on additives with some kind of sum. Now link qryCatScoreByProject to qryAdditiveScoresByProject. You will then have a query that has the sum of categories linked to the project sum of additives. In that query add the category sum and additive sum. These are fast simple queries, do not store anything.
 

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
Thanks.
So for the Grand Total do this?
1609890808415.png
 

Attachments

  • 1609890550447.png
    1609890550447.png
    35.1 KB · Views: 126

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
Ah.... I see!
I didn't see your last post before I posted my question.
Thanks for the help.
Very nice!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
It gets really complicated to do a calculated control based on a calculated control especially if that other calculation is on a different form. That is why I would go with a dlookup instead. I call these wrapper functions where I wrap an access domain function like a dlookup, dsum, dcount with a UDF to quickly get a field value. Normally the thing I pass in is the primary key. The nice thing you can write once and use everywhere. Also they are easy to debug, because you can pass in literal values like
debug.print GetCatScore(1)

=GetCatScore([projectID]) + getAdditiveScore([ProjectID])

But if you build the two aggregate queries and join them you should be able to make a query with projectID and GrandTotal.
then for the form
=GetGrandTotal([projectID])

and make a wrapper function as shown.
 

Weekleyba

Registered User.
Local time
Today, 10:16
Joined
Oct 10, 2013
Messages
586
I ran into a snag when setting up the Additives_F, which is the second subform.
Background: I had at first, set up my form incorrectly (not normalized) with all the Category and Additive items listed separately in one table. Plog showed me the errors of my ways. So I fixed the Categories by using continuous subform as shown below.
Ok, with that said, I'm on to fix the Additives.
Similarly, I want to create a subform, but the scores for each additive item (i.e. Urgency, Enhance Infection Control, etc) are calculated differently, as shown below.
For example, the Urgency was done in query expression like this:
AddUrgency: IIf([Urgency]=0,0,IIf([Urgency]<6,4,IIf([Urgency]<12,3,IIf([Urgency]<18,2,IIf([Urgency]=18,1,IIf([Urgency]<24,1,IIf([Urgency]=24,1,IIf([Urgency]>24,0,0))))))))
GeneralSafety was done in the same query expression:
AddGenSafety: IIf([GenSafety]<5,0,IIf([GenSafety]<33,1,IIf([GenSafety]<62,2,IIf([GenSafety]<90,3,IIf([GenSafety]<101,4,0)))))

But now I'm stuck on how to do this when I change to a continuous subform for the Additives.


1609951036346.png


Here's what I have so far.
The new Additive_F to be inserted as a subform, linked with ProjectID.
1609952498476.png


The Additive_F record source is pas_Additive_Q, which is fed by ProjectADDScores_T. As shown below.

1609952618094.png


How do accomplish calculating the individual Additive scores base on the user input data with this new arrangement?
 

Attachments

  • 1609952471372.png
    1609952471372.png
    17.2 KB · Views: 122

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
You do this with a table and not IIF

tblAdditiveScore
AdditiveID
RangeStart
RandEnd
Score

example assume Urgency has ID of 1
1 0 0 0
1 .1 5.9 4
1 6 11.9 3

Then no code only a sql query doing a between rangeStart and rangeEnd
Way easier to code and manage.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2002
Messages
42,970
You don't have to use domain functions. You can use simple =Sum(xxx) in controls in the subform's footer.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
You don't have to use domain functions. You can use simple =Sum(xxx) in controls in the subform's footer.
I like to see it. As I said it can be done, but to sum the grand total in a calculated control on the main form where you sum two different subforms, the syntax will be a bear. Overly complex IMO.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
Looking at your ranges you may have a mistake
IIf([Urgency]=0,0,
IIf([Urgency]<6,4,
IIf([Urgency]<12,3,
IIf([Urgency]<18,2,

IIf([Urgency]=18,1,
IIf([Urgency]<24,1,
IIf([Urgency]=24,1,
IIf([Urgency]>24,0,0

equal to 24
less than 24
and equal to 18 all equal 1?

This is the same as
18<= x <=24 ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2002
Messages
42,970
I like to see it. As I said it can be done, but to sum the grand total in a calculated control on the main form where you sum two different subforms, the syntax will be a bear. Overly complex IMO.
Nope. The syntax doesn't change. You just repeat the calculation
In the footer:
=Sum((fldA + fldB) * fldC)

In the grand total
=Sum((fldA + fldB) * fldC)

You just repeat the calculation. You CANNOT reference the control. It may possible that you can reference controls that are in the form's footer since they don't repeat. They only occur once. So the following might work

=Sum(controlX)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
Nope. The syntax doesn't change. You just repeat the calculation
In the footer:
=Sum((fldA + fldB) * fldC)
No it would not. Likely would look like
Code:
=Sum(([subfrmAControl].form.FieldA + [SubfrmAControl].Form.FieldB) * [subfrmAControl].Form.FieldC)) + sum([SubfrmBControl].form.Fieldz * [subformBControl].form.Fieldx)

And I would not waste my time on the syntax.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:16
Joined
May 21, 2018
Messages
8,463
So if the table of ranges looks like this for Urgenct (id = 1)

AdditiveRanges AdditiveRanges

AdditiveID_FKRangeStartRangeEndScore
1​
0​
0.1​
0​
1​
0.1​
6​
4​
1​
6​
12​
3​
1​
12​
18​
2​
1​
18​
24.1​
1​
1​
24.1​
99999999999​
0​
Code:
SELECT tblurgency.addid,
       tblurgency.val,
       additiveranges.score
FROM   tblurgency
       INNER JOIN additiveranges
               ON tblurgency.addid = additiveranges.additiveid_fk
WHERE  (( ( tblurgency.val ) >= [rangestart]
          AND ( tblurgency.val ) < [rangeend] ))
ORDER  BY tblurgency.val;

Query1 Query1

addIDValScore
1​
0​
0​
1​
1​
4​
1​
3​
4​
1​
6​
3​
1​
8​
3​
1​
12​
2​
1​
15​
2​
1​
18​
1​
1​
22​
1​
1​
24​
1​
1​
30​
0​

now you just add your other additives. FYI, I consistently do my ranges >= Start and <End. Also if it is integer you can be more liberal.
 
Last edited:

Users who are viewing this thread

Top Bottom