Trouble with Count function on report

  • Thread starter Thread starter Ron V
  • Start date Start date
R

Ron V

Hi gang,

I am stuck on this issue of trying to get this formula in
a report to function properly.
This is the original formula:

=IIf(Nz([Demeanor]) Is Not Null,Count([Demeanor])*2)

It looks at the values in each Demeanor field to determine
if it is empty or not. If it's not empty then it will
count the # of records where there is a value in the
Demeanor field and multiply it by 2 to give a total
possible point value that could have been achieved.
Now what I need to do is to seperate those records
according to the field [MonitorTypeID], and just do a
count on the possible points of those records where if the
field [MonitorTypeID]=2.
I would somehow need to incorporate this partial formula:
IIf([MonitorTypeID]=2,Count[Demeanor],0))

into the formula above but I've tried & failed. I can't
seem to get the proper sequence so that it just counts and
doubles only those records where [MonitorTypeID]=2.
A point to note here is that the Nz function is an
important part of the formula.

Any ideas? Thanks in advance for the help.

Ron
 
Duane - Thanks for the reply, but I would need to Count
the [Demeanor] records where the MonitorIDType field =
Remote (Which is assigned a value of 2). I can't Sum the
values of Demeanor in this new field called [CountDemeanor]
because that won't give me an accurate count of the
possible amount of points for each record because someone
could have scored a zero in the Demeanor field and that
would still count towards the total value of points
possible for that group of records.

Maybe this would make more sense:

A score in the Demeanor field could either be a 2, 0 or
Null (score does not apply).
If a 2 is scored then that counts as a 2
If a 0 is scored it counts as a 0 in the total points
received but counts as a 2 in the total points possible
field. This is where I need to do a count of the records
instead of a sum of the points. I can take all the
records where the [MonitorTypeID]=Remote(2) and then count
them looking at Demeanor and multiply by 2.
Does that help. I know what I want it to do but not how
to execute it.
Can you help again?
Here's the 2 seperate formula's again:
=IIf(Nz([Demeanor]) Is Not Null,Count([Demeanor])*2)
=IIf([MonitorTypeID]=2,Count[Demeanor],0))

Thanks.

Ron
-----Original Message-----
Try:
=Sum(IsNull([Demeanor])+1)*2

--
_____________
Duane Hookom
MS Access MVP

Ron V said:
Hi gang,

I am stuck on this issue of trying to get this formula in
a report to function properly.
This is the original formula:

=IIf(Nz([Demeanor]) Is Not Null,Count([Demeanor])*2)

It looks at the values in each Demeanor field to determine
if it is empty or not. If it's not empty then it will
count the # of records where there is a value in the
Demeanor field and multiply it by 2 to give a total
possible point value that could have been achieved.
Now what I need to do is to seperate those records
according to the field [MonitorTypeID], and just do a
count on the possible points of those records where if the
field [MonitorTypeID]=2.
I would somehow need to incorporate this partial formula:
IIf([MonitorTypeID]=2,Count[Demeanor],0))

into the formula above but I've tried & failed. I can't
seem to get the proper sequence so that it just counts and
doubles only those records where [MonitorTypeID]=2.
A point to note here is that the Nz function is an
important part of the formula.

Any ideas? Thanks in advance for the help.

Ron
Click to expand...


.
Click to expand...
 
Try:
=Sum( (IsNull([Demeanor]) AND MonitoryIDType=2)+1)*2

To count the number of records that match a true/false expression, you can
use syntax like:
=Abs( Sum( [True/False Expession] ) )
To total a field for records matching a true/false expression, use:
=Abs( Sum( [True/False Expession] * [FieldToSum]) )

--
Duane Hookom
Microsoft Access MVP


Ron V said:
Duane - Thanks for the reply, but I would need to Count
the [Demeanor] records where the MonitorIDType field =
Remote (Which is assigned a value of 2). I can't Sum the
values of Demeanor in this new field called [CountDemeanor]
because that won't give me an accurate count of the
possible amount of points for each record because someone
could have scored a zero in the Demeanor field and that
would still count towards the total value of points
possible for that group of records.

Maybe this would make more sense:

A score in the Demeanor field could either be a 2, 0 or
Null (score does not apply).
If a 2 is scored then that counts as a 2
If a 0 is scored it counts as a 0 in the total points
received but counts as a 2 in the total points possible
field. This is where I need to do a count of the records
instead of a sum of the points. I can take all the
records where the [MonitorTypeID]=Remote(2) and then count
them looking at Demeanor and multiply by 2.
Does that help. I know what I want it to do but not how
to execute it.
Can you help again?
Here's the 2 seperate formula's again:
=IIf(Nz([Demeanor]) Is Not Null,Count([Demeanor])*2)
=IIf([MonitorTypeID]=2,Count[Demeanor],0))

Thanks.

Ron
-----Original Message-----
Try:
=Sum(IsNull([Demeanor])+1)*2

--
_____________
Duane Hookom
MS Access MVP

Ron V said:
Hi gang,

I am stuck on this issue of trying to get this formula in
a report to function properly.
This is the original formula:

=IIf(Nz([Demeanor]) Is Not Null,Count([Demeanor])*2)

It looks at the values in each Demeanor field to determine
if it is empty or not. If it's not empty then it will
count the # of records where there is a value in the
Demeanor field and multiply it by 2 to give a total
possible point value that could have been achieved.
Now what I need to do is to seperate those records
according to the field [MonitorTypeID], and just do a
count on the possible points of those records where if the
field [MonitorTypeID]=2.
I would somehow need to incorporate this partial formula:
IIf([MonitorTypeID]=2,Count[Demeanor],0))

into the formula above but I've tried & failed. I can't
seem to get the proper sequence so that it just counts and
doubles only those records where [MonitorTypeID]=2.
A point to note here is that the Nz function is an
important part of the formula.

Any ideas? Thanks in advance for the help.

Ron
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top