Calculate a running sum in a form

[Guest]

Hello,

I need to know if there is a simple way to calculate the sum of two fields
(in a sub-form) which contain a numeric values. The form in general is a
botanical survey with the main form containing fields such as SiteID,
SurveyorID, SurveyNo, SurveyDate; with the sub-form, related via SurveyNo,
containing the following: PlantName (pick list of species),and three yes/no
fields (Frequent, Occasional and Rare) only one can be checked to indicate
how often each plant occurs within the Site. They are yes/no on the form but
actually add a numeric value to the underlying table (e.g. yes in Frequent =
2, Yes in Occasional = 1).
On the main form I want to add a calculate control that sums the values from
only the Frequent and Occasional fields. I have had a look on google and
understand that this can be achieved with script, but I was hoping a simpler
approach is possilbe where i can use a simple expression in the control
property to complete this calculation, as I am a bit of a novice.

Regards

Luke

 

[Steve Schapel]

Luke,

This is certainly possible. However, the details of how to do it will
depend on certain things that are not clear from your post. You
mentioned "three yes/no fields", but I would suspect this is not the
case. It really sounds like you just have one field, maybe it's called
Frequency or some such, and this field is represented on the form by an
Option Group control, which has 3 checkboxes within it for the selection
between Frequent, Occasional and Rare (option values 2,1,0). On the
assumption that this is what you've got, then put an unbound textbox in
the Form Header or Form Footer section of the subform. Then put the
equivalent of this expression into the Control Source property of the
textbox...
=-Sum([Frequency]>0)

 

[Guest]

Steve

Thank you very much for the help much appreciated. Your response has made me
reconsider the lay-out of the form in question. I was'nt completely clear on
my first post, but as it stood my form contained the following fields:
SurveyNo, SpeciesNo (which is related to another table with a list of plant
species), Frequency (a numeric value between 1-20 that represents the number
of times an indiviudal plant is recorded at fixed locations across the survey
site), Frequent, Occassional, and Rare (numeric fields that are updated by a
"SetValue macro" in the AfterUpdateValue event, according to which of the
following values are entered in the Frequency field: <5 = 1 (yes) in Rare
Field, 5-8 = 1 (yes) in Occassional Field, >8 = 2 (yes) in the Frequent
Field).

I have now rationaised it to four fields SurveyNo, SpeciesNo, Frequency, and
FrequencyText. Whith FrequencyText using the SetValue Macro to display Rare
(0), Occassional (1) or Frequent (2) according to the above conditions when a
value is entered in the frequency field.

I have now set up two calculate controls in the form footer that calculate:
the sum of the plants occuring Frequently, and those occuring Occasionally.

The final output I want is a calculate control that says wether the
SurveySite is in good botanical condition based upon the following condtions:
The Box says "Yes" where there are at least two plants occuring Frequently
and Two occuring Occasionally, or at least three occruing frequently and one
occasionaly, or at least four occuring frquently.
The difficulty I have is that Frequent needs a greater weighting than
Occasional as the following conditons would not be suffcient for the
SurveySite to be in good condition: 3 Occassionals and one Frequent, more
than Four Occassionals and no Frequents.

I would not know where to begin with the syntax to create such a complex
condtional expression!! presuming it is possible. Any additonal help with
this would be greatly appreciated.

Regards

Luke

Luke,

This is certainly possible. However, the details of how to do it will
depend on certain things that are not clear from your post. You
mentioned "three yes/no fields", but I would suspect this is not the
case. It really sounds like you just have one field, maybe it's called
Frequency or some such, and this field is represented on the form by an
Option Group control, which has 3 checkboxes within it for the selection
between Frequent, Occasional and Rare (option values 2,1,0). On the
assumption that this is what you've got, then put an unbound textbox in
the Form Header or Form Footer section of the subform. Then put the
equivalent of this expression into the Control Source property of the
textbox...
=-Sum([Frequency]>0)

--
Steve Schapel, Microsoft Access MVP

Hello,

I need to know if there is a simple way to calculate the sum of two fields
(in a sub-form) which contain a numeric values. The form in general is a
botanical survey with the main form containing fields such as SiteID,
SurveyorID, SurveyNo, SurveyDate; with the sub-form, related via SurveyNo,
containing the following: PlantName (pick list of species),and three yes/no
fields (Frequent, Occasional and Rare) only one can be checked to indicate
how often each plant occurs within the Site. They are yes/no on the form but
actually add a numeric value to the underlying table (e.g. yes in Frequent =
2, Yes in Occasional = 1).
On the main form I want to add a calculate control that sums the values from
only the Frequent and Occasional fields. I have had a look on google and
understand that this can be achieved with script, but I was hoping a simpler
approach is possilbe where i can use a simple expression in the control
property to complete this calculation, as I am a bit of a novice.

Regards

Luke

 

[Steve Schapel]

Luke,

Thanks for the further explanation of your project.

GOod that you modified the table design. It is a step in the right
direction. In fact, you should also dispense with the FrequencyText
field. This is derived data and as such it violates database design
principles relating to data redundancy to store this in a table. This
should be calculated "on the fly" whenever you need it, either in a
query, or in this case in a calculated control on the form.

Anyway, either way we should be able to come up with a further
expression to calculate the required Good Condition status. However,
I'm sorry I don't quite understand the bit about the weighting. How
would that work? What are the conditions for Good Condition if there is
1 or 0 Frequents?

 

[Guest]

Steve

Sorry that was all a bit confusing I must confess, what good botanical
condition means is where a SurveySite contains at a minimum at least two
plants that occur at frequent or above and at least two plants that oocur at
occasional or above. The weighting was misleading, what I was trying to
explain (badly!) was that three frequents and one occassional = good
condition, four frequents and 0 occassionals = good condition, but three
frequents and 0 occassionals does not; similarly three occasionals and 1
frequent is not good condition, neither is 10 occassionals if there is not at
least two frequents.

I am struggling to explain it in English so I would'nt know where to begin
in a expression.

Once again thanks for the help

Luke

 

[Steve Schapel]

Luke,

Funny you should mention "struggling to explain it in English". You
will often see reference to the fact that a key to effective expression
writing is the ability to express the "rules" in ordinary language.

So, if I understand you correctly, this will do it...

=IIf(Sum([FrequencyText]="Frequent")<=-4 Or
(Sum([FrequencyText]="Frequent")<=-3 And
Sum([FrequencyText]="Occasional")<=-1) Or
(Sum([FrequencyText]="Frequent")<=-2 And
Sum([FrequencyText]="Occasional")<=-2),"Yes","No")

Or, to simplify it slightly, you could set the Format property of the
textbox to Yes/No, and then use this expression...
=Sum([FrequencyText]="Frequent")<=-4 Or
(Sum([FrequencyText]="Frequent")<=-3 And
Sum([FrequencyText]="Occasional")<=-1) Or
(Sum([FrequencyText]="Frequent")<=-2 And
Sum([FrequencyText]="Occasional")<=-2)

Or, to dispense with the redundant FrequencyText field, as I suggested
earlier, like this...
=Sum([Frequency]>8)<=-4 Or (Sum([Frequency]>8)<=-3 And Sum([Frequency]
Between 5 And 8)<=-1) Or (Sum([Frequency]>8)<=-2 And Sum([Frequency]
Between 5 And 8)<=-2)

A lot of the above relies on the fact that Access evaluates True to a
default value of -1. There are other ways to achieve the saem result,
for example using the IIf() or Switch() functions, but they tend to be
more long-winded.

 

[Guest]

Steve,

Thanks very much for the expressions, I have removed the FrequencyText field
and replaced with an unbound control, all would now appear to work fine.

Once again thanks for your help

Many regards

Luke

Luke,

Funny you should mention "struggling to explain it in English". You
will often see reference to the fact that a key to effective expression
writing is the ability to express the "rules" in ordinary language.

So, if I understand you correctly, this will do it...

=IIf(Sum([FrequencyText]="Frequent")<=-4 Or
(Sum([FrequencyText]="Frequent")<=-3 And
Sum([FrequencyText]="Occasional")<=-1) Or
(Sum([FrequencyText]="Frequent")<=-2 And
Sum([FrequencyText]="Occasional")<=-2),"Yes","No")

Or, to simplify it slightly, you could set the Format property of the
textbox to Yes/No, and then use this expression...
=Sum([FrequencyText]="Frequent")<=-4 Or
(Sum([FrequencyText]="Frequent")<=-3 And
Sum([FrequencyText]="Occasional")<=-1) Or
(Sum([FrequencyText]="Frequent")<=-2 And
Sum([FrequencyText]="Occasional")<=-2)

Or, to dispense with the redundant FrequencyText field, as I suggested
earlier, like this...
=Sum([Frequency]>8)<=-4 Or (Sum([Frequency]>8)<=-3 And Sum([Frequency]
Between 5 And 8)<=-1) Or (Sum([Frequency]>8)<=-2 And Sum([Frequency]
Between 5 And 8)<=-2)

A lot of the above relies on the fact that Access evaluates True to a
default value of -1. There are other ways to achieve the saem result,
for example using the IIf() or Switch() functions, but they tend to be
more long-winded.

--
Steve Schapel, Microsoft Access MVP

Steve

Sorry that was all a bit confusing I must confess, what good botanical
condition means is where a SurveySite contains at a minimum at least two
plants that occur at frequent or above and at least two plants that oocur at
occasional or above. The weighting was misleading, what I was trying to
explain (badly!) was that three frequents and one occassional = good
condition, four frequents and 0 occassionals = good condition, but three
frequents and 0 occassionals does not; similarly three occasionals and 1
frequent is not good condition, neither is 10 occassionals if there is not at
least two frequents.

I am struggling to explain it in English so I would'nt know where to begin
in a expression.

Once again thanks for the help

Luke

 

[Steve Schapel]

Thanks for letting us know, Luke, and glad to hear of your success.