Solved How to calculate according to conditions at the footer of the subForm?

[yunhsuan]

Hello!
There are several records in a subForm, like below. At the footer of the subForm, I want to calculate PRICE according to conditions, the total price of condition "a" minus The total price of condition "b".
For example, the condition of No. 1, 2, 4 is "a", and the condition of No. 3 is "b". So, the result should be (100+150+120)-100=270.
No. PRICE Con.
1 100 a
2 150 a
3 100 b
4 120 a
How can I do this?
Thanks in advance!

 

[CJ_London]

not following the requirement - your example is too vague

Are conditions controls? if so what type of control? subform? textbox? something else? are they bound or unbound?

can the conditions change from a to b?

 

[Pat Hartman]

Add a control to the footer of the subform:
= Sum(IIf(Condition = "b", Amt * -1, Amt))

 

[oleronesoftwares]

Here is a walk around.

1. Create two textboxes in subform(one for prices if condition A, second is prices if condition b)
2. Create 3 textboxes at the footer of the subform.
A. nz(sum(form![pricecondition_a]))// sum of price if conditon A
B. nz(sum(form![pricecondition_b]))// sum of price if conditon B
C. nz(sum(form![pricecondition_a]))-sum(form![pricecondition_b]) // diference of the two prices

3. Finally on the main form, create a textbox that has its control source been 2C

The above if modified can produce the result

 

[The_Doc_Man]

This can be done in a divide-and-conquer situation, but the BEST way to do it is to recognize that you can just write a query or function to do this. Your problem becomes much more complex if there are more than two conditions that would be additive. I.e. if conditions A and D cause you to add values. It doesn't matter if other conditions cause you to subtract.

Let's say (for discussion's sake) that the table driving the subform is called SUBTABLE and the condition field is SUBCOND and the price field is SUBPRICE. Now, this is in a footer, so it will be grouped by something, call it SUBGROUP which can be X, Y, or Z. IF there are only conditions A and B in the SUBCOND field, ...

Now in your main form where you want to find this value,

MyPrice = DSum( "(SUBPRICE * IIF( SUBCOND= '" & A & "', +1, -1 )", "SUBTABLE", "SUBGROUP = '" & X & "'" )

 

[Pat Hartman]

One text box or three??? Your choice. This really isn't a contest oleronessoftwares.

= Sum(IIf(Condition = "b", Nz(Amt,0) * -1, Nz(Amt,0))) would have been a rational improvement.

 

[Pat Hartman]

Not sure why you need a domain function Doc when the data is in the form/report's recordsource already and Sum() will work. If you want to show it on the main form, that is easy enough to do also without a domain function.

I guess I was wrong about this being a contest.

 

[The_Doc_Man]

Pat, I was working in an abstract, but the question is whether that sub-form is linked to something in the main. I was contemplating how hard or easy it would be to track down the data in the case where the sub-form is linked because in that case, the sub-form's .RecordSource doesn't tell the whole story. Or at least, I don't THINK it does. I don't THINK that Access changes the stored .RecordSource when there is a linked parent/child field. Am I wrong on that?

 

[CJ_London]

I don't THINK that Access changes the stored .RecordSource when there is a linked parent/child field. Am I wrong on that?

funnily enough just posted this thread about subform recordsources

do the linkchild/master subform properties filter the underlying data or apply criteria?

The reason I'm asking is that I've was told many years ago when first setting out on the Access learning curve that they apply a filter and reviewing a number of posts on this forum, many others think the same thing. Typically 'as a demonstration remove these properties and all the records are...

www.access-programmers.co.uk

 

[Pat Hartman]

Yes, Access filters the recordset of the subform by the FK. If the master/child links are not defined, no filtering happens. This is probably what CJ's link said but I repeated it here for convenience. I would guess, but I do not know for certain that the filtering is actually done with criteria on the query. Otherwise, this would be extremely inefficient for RDBMS BE's.

 

[CJ_London]

this is probably what CJ's link said

NO, it doesn't - it would appear it applies a criteria and not filter an existing dataset, Common assumption and advice is that it applies a filter - as you are saying, My link is questioning the validity of that assumption.

 

[Pat Hartman]

I just looked at your link. You are showing SQL that is sent to the server. It includes criteria. That means to me that there is no Access "filtering" going on. Access is modifying your SQL to add criteria to it which is significantly more efficient than filtering when the BE is RDBMS.