Counting and summing in reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am new to doing calculations in Access, and am having some difficulties.
Here is my situation:

I have a report that shows data for each child in a class. One field is
called "IN 1". this field may have a value between 28 and 72. In the Class
footer, I have a field called "Concerns" where I want to count the number of
children who fall within a particular range in the "IN 1" field. E.G. If
John's value in "IN 1" is between 28 and 40, I want to count him in the
"Concerns" field otherwise ignore him and the same for every other child in
the class. I'm not sure how to set this up to calculate properly.

Secondly, I have 3 of these calculating range fields so I can get the counts
for 3 different ranges. In a forth field called "Total", I want to sum the
counts that are returned in the aforementioned range fields. E.G. If 6
children fall within the first range (concerns), and 2 children fall in the
2nd range (Average), and 1 child is in the 3rd range (No Problems), I want my
"Total" field to add up those numbers.

Can anyone help me with these calculating issues?

Thank you in advance for helping me out.
 
You can count the occurance of an expression like:
=Sum(Abs(...your expression...))
Replace the "your expression" with any expression that evaluates to either
true or false. In your case, try:
=Sum(Abs([IN 1] between 28 and 40))

You can add controls within the same section or combine control sources.
 
SVE said:
I am new to doing calculations in Access, and am having some difficulties.
Here is my situation:

I have a report that shows data for each child in a class. One field is
called "IN 1". this field may have a value between 28 and 72. In the Class
footer, I have a field called "Concerns" where I want to count the number of
children who fall within a particular range in the "IN 1" field. E.G. If
John's value in "IN 1" is between 28 and 40, I want to count him in the
"Concerns" field otherwise ignore him and the same for every other child in
the class. I'm not sure how to set this up to calculate properly.

Secondly, I have 3 of these calculating range fields so I can get the counts
for 3 different ranges. In a forth field called "Total", I want to sum the
counts that are returned in the aforementioned range fields. E.G. If 6
children fall within the first range (concerns), and 2 children fall in the
2nd range (Average), and 1 child is in the 3rd range (No Problems), I want my
"Total" field to add up those numbers.
Click to expand...


The quick and dirty approach is for the Concerns text box to
use an expression like:
=Sum(IIf([IN 1] Between 28 And 40, 1, 0))
Similarly fo the Average and No Problems text boxes.

Then the Total text box would just use the expression:
=Concerns + Average + [No Problems]

A better practice would be to have a table with the ranges:

Table Ranges
RangeName Text
RangeLow Long
RangeHigh Long

with 3 records:
Concern 28 40
Average 41 60
NoProblem 61 72

and use a series of queries to calculate the summary
information in a subreport. Based on your question, I
suggest that you only keep this thought in mind and. for
now, avoid getting caught up in a complex technique just for
the sake of a long term software maintenace issue.
 
Back
Top