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?

 

[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 & "'" )

 

[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

 

[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.