Count Formula

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

Guest

Hello all,

I have a report which contains the status of all the student activities.
The information in the report comes from a table.

The report is grouped by the type of student group. In the report, I have
total of 5 columns for different types of activities. If a student in a
group does a community service, for example, the report would show an "X"
under the community service column.

I am trying to write a formula to count each type of activity (X) of each
group. The count formula is "=count([community service])" in the Student
Group Footer section. For some reason, 3 of the student activity counts
work, and the other 2 are not working.

An example of the 3 that works is if 2 students do community services (2
X's) in Group A, the group footer shows 2 under the community service column.

An example of the other 2 is they show the count of # of students in the
group, instead of # of X's for the type of activities.

I even tried the formula: iif([community service]="X",count([community
service]),0). Again, the outcomes of 5 are not consistent.

Any help would be appreciated.
 
AccessHelp said:
I have a report which contains the status of all the student activities.
The information in the report comes from a table.

The report is grouped by the type of student group. In the report, I have
total of 5 columns for different types of activities. If a student in a
group does a community service, for example, the report would show an "X"
under the community service column.

I am trying to write a formula to count each type of activity (X) of each
group. The count formula is "=count([community service])" in the Student
Group Footer section. For some reason, 3 of the student activity counts
work, and the other 2 are not working.

An example of the 3 that works is if 2 students do community services (2
X's) in Group A, the group footer shows 2 under the community service column.

An example of the other 2 is they show the count of # of students in the
group, instead of # of X's for the type of activities.

I even tried the formula: iif([community service]="X",count([community
service]),0). Again, the outcomes of 5 are not consistent.
Click to expand...


It depends on what you have in the table, not what you are
displaying in the report. Since you did not explain what
the fields are in the table or the actual values in the
fields, I can only guess.

If you have 5 text fields, one for each activity (an
un-normalized data structure by the way), and each field has
an "X" to indicate participation, then I suspect that some
of the fields have a space or just a zero length string. I
suspect that the reason you are counting all records is
because Count counts all non-Null values, not just the Xs.

Under all my assumptions, you can count the Xs this way:
=Sum(IIf([community service]="X", 1, 0))
 
Hi Marshall,

Thanks very much for your help. It works. FYI, the X's (and "") are
populated using an update query.

Can you help me one more thing? I have a field in a table called "No X".
Basically, I am trying to populate an "X" in the field on a student using an
update query if the student does not have any activity (No "X's" in those 5
columns).

I tried this formula:

iif([Community Service]<>"X" and [Column A]<>"X" and etc.,"X","").

Thanks very much for your help again.

Marshall Barton said:
AccessHelp said:
I have a report which contains the status of all the student activities.
The information in the report comes from a table.

The report is grouped by the type of student group. In the report, I have
total of 5 columns for different types of activities. If a student in a
group does a community service, for example, the report would show an "X"
under the community service column.

I am trying to write a formula to count each type of activity (X) of each
group. The count formula is "=count([community service])" in the Student
Group Footer section. For some reason, 3 of the student activity counts
work, and the other 2 are not working.

An example of the 3 that works is if 2 students do community services (2
X's) in Group A, the group footer shows 2 under the community service column.

An example of the other 2 is they show the count of # of students in the
group, instead of # of X's for the type of activities.

I even tried the formula: iif([community service]="X",count([community
service]),0). Again, the outcomes of 5 are not consistent.
Click to expand...


It depends on what you have in the table, not what you are
displaying in the report. Since you did not explain what
the fields are in the table or the actual values in the
fields, I can only guess.

If you have 5 text fields, one for each activity (an
un-normalized data structure by the way), and each field has
an "X" to indicate participation, then I suspect that some
of the fields have a space or just a zero length string. I
suspect that the reason you are counting all records is
because Count counts all non-Null values, not just the Xs.

Under all my assumptions, you can count the Xs this way:
=Sum(IIf([community service]="X", 1, 0))
Click to expand...
 
AccessHelp said:
Thanks very much for your help. It works. FYI, the X's (and "") are
populated using an update query.

Can you help me one more thing? I have a field in a table called "No X".
Basically, I am trying to populate an "X" in the field on a student using an
update query if the student does not have any activity (No "X's" in those 5
columns).

I tried this formula:

iif([Community Service]<>"X" and [Column A]<>"X" and etc.,"X","").
Click to expand...


Wrong idea! Access is a database system, not a spreadsheet.

You should never have a field in a table where its value can
be determined from other fields in the same record.
Instead, you need to calculate the value whenever you want
to display on a form or report.

A text box expression that will do that is:

=Not (Nz([Community Service],"")="X" Or Nz([Column
A],"")="X" Or . . . )

which will be True or False. To make it display an X when
the expression evaluates to True, set the text box's Format
property to the custom format:
"";"X";""

I will reiterate that this set of activity fields is a bad
design. The set of questions we are dealing with now are
caused by your un-normalized table design and will only get
worse as you try to do more things with it.

Instead of multple activities in the person table, you
should have a separate PersonActivities table with at least
two fields, one for the activity identifier and the other
with the PK field for the person that has done the activity.
See if you can find the topics Database Normalization and
Many to Many relationships in Help or through Google for
more details. If you continue to use your current
arrangement, the problems will just get more difficult and
you will find fewer and fewer people that have any
experience in dealing with the wrong way of doing things.
 
Marshall
I was wondering if you could help me see what I am doing wrong. After
looking at the various answers on this subject your's make the most sense to
me.

I am creating a report for faculty that will give them the blind count of
students by gender, ethnic category, and age. I tried to use the following
formula in the Gender header to count the Fs and Ms to get the male female
number. However I keep getting two consistant numbers, that do not addup at
all:

=Count(IIf([dbo_APP_Roster.Gender]="F", 1, 0))
I tried this same formula using Sum as well, and get an even werider number.

I keep getting 57 which does not add up to even the males and females
together. What am I doing wrong, can you tell?
Thanks in advance for your help.
Jacqueline
 
Yes, there is a possibility for nulls, if the data is not obtained he entry
person has nothing to fill the field with.
--
Jacqueline


M Skabialka said:
Do you have any null values under Gender?

Jacqueline said:
Marshall
I was wondering if you could help me see what I am doing wrong. After
looking at the various answers on this subject your's make the most sense
to
me.

I am creating a report for faculty that will give them the blind count of
students by gender, ethnic category, and age. I tried to use the following
formula in the Gender header to count the Fs and Ms to get the male female
number. However I keep getting two consistant numbers, that do not addup
at
all:

=Count(IIf([dbo_APP_Roster.Gender]="F", 1, 0))
I tried this same formula using Sum as well, and get an even werider
number.

I keep getting 57 which does not add up to even the males and females
together. What am I doing wrong, can you tell?
Thanks in advance for your help.
Jacqueline
Click to expand...
Click to expand...
 
Back
Top