COUNT RECORDS WITH BOOLEEN

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nick - 08 Dec 2005 06:29 GMT

I have a report with three column's, each with a number value when selected.
They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
number of "2" and (3)Other illness with a number of 3. How can I get a total
for each column? I have tried this in the unbound text box
=Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
each column is the value of each Booleen field.

Reply to this Message

Jeff Boyce - 08 Dec 2005 13:48 GMT

Nick

Consider a "Totals" query, grouped by the values in the field that holds
these 3 values.

Signature

Regards

Jeff Boyce
<Office/Access MVP>

> I have a report with three column's, each with a number value when selected.
> They are (1)Injury with a numeric value of "1", (2)Skin disorder with a
> number of "2" and (3)Other illness with a number of 3. How can I get a total
> for each column? I have tried this in the unbound text box
> =Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
> each column is the value of each Booleen field.

Reply to this Message

Nick - 08 Dec 2005 16:12 GMT

After a closer look at the query on the report, I found this (1)Injury:
IIf([Injury1]=1,"X",""). Now how do I get a totals for each column based on
this one field?

> Nick
>
[quoted text clipped - 9 lines]
> > =Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
> > each column is the value of each Booleen field.

Reply to this Message

Jeff Boyce - 09 Dec 2005 13:06 GMT

Nick

I'm not there, I don't know what you mean by "for each column".

Are you saying that you have more than one field in the underlying table, or
data displayed in more than one column on your report? Are you working with
a cross-tab report?

Signature

More info, please ...

Jeff Boyce
<Office/Access MVP>

> After a closer look at the query on the report, I found this (1)Injury:
> IIf([Injury1]=1,"X",""). Now how do I get a totals for each column based on
[quoted text clipped - 13 lines]
> > > =Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
> > > each column is the value of each Booleen field.

Reply to this Message

John Spencer - 08 Dec 2005 14:07 GMT

Try the following to count the number of times [(1)Injury] = 1
=Abs(Sum([(1)Injury]=1))

each column is the value of each Booleen field.

>I have a report with three column's, each with a number value when
>selected.
[quoted text clipped - 4 lines]
> =Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
> each column is the value of each Booleen field.

Reply to this Message

Michel Walsh - 08 Dec 2005 20:54 GMT

Hi,

If you are in a detail section and wish for a total of these details, use
DSUM syntax

=DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

You can also use the third argument if you want to restrict the sum to a
group.

Hoping it may help,
Vanderghast, Access MVP

Reply to this Message

Nick - 08 Dec 2005 22:39 GMT

I did try your suggestion but get an error message on the report. I am not
sure I put in the right "TablenameHere" in the statment.
Thanks

> Hi,
>
[quoted text clipped - 17 lines]
> > =Sum(IIf([(1)Injury],1,0)) and on down the line but the value returned in
> > each column is the value of each Booleen field.

Reply to this Message

Michel Walsh - 09 Dec 2005 11:15 GMT

Hi,

You can try the expression in the Debug Immediate Window.

? DSUM("iif([(1)injury], 1, 0)", "TablenameHere" )

It may be easier and faster to experiment there.

You have to use the real table name of the table you really use. I also
assume the real field name is exactly (1)injury without space. When we
use [ ], the name inside it must be exactly match the (field) name, with
space in it, if any.

Hoping it may help
Vanderghast, Access MVP

>I did try your suggestion but get an error message on the report. I am not
> sure I put in the right "TablenameHere" in the statment.
[quoted text clipped - 22 lines]
>> > in
>> > each column is the value of each Booleen field.

Reply to this Message

Nick - 09 Dec 2005 15:37 GMT

Thanks,
I am using this equation and still get an error message.
=DSum("iif([(1)injury],1, 0)","[Injury1]") The actual table name is Injury1
and it is the foundation of the other three (2)Skin Disorder:
IIf([Injury1]=2,"X","") (2)Skin Disorder, and (3)Respitory Condition:
IIf([Injury1]=3,"X","") (3)Respitory Condition.
I certain I have overlooked something but do not know what it is.

> Hi,
>
[quoted text clipped - 38 lines]
> >> > in
> >> > each column is the value of each Booleen field.

Reply to this Message

Michel Walsh - 09 Dec 2005 18:29 GMT

Hi,

The first argument of the iif should involved the exact field name. Is the
field name is [injury] or [(1)Injury]. I assumed it was (1)Injury, and
that you were not to make the comparison. Now, it sounds like the field name
is Injury and that we have to make the comparison, so, I would try:

= DSum( " iif( injury = 1, 1, 0 ) " , "Injury1" )

Since Injury and Injury1 are valid names, we don't have to use [ ] around
them, in that case.

Hoping it may help,
Vanderghast, Access MVP

> Thanks,
> I am using this equation and still get an error message.
[quoted text clipped - 54 lines]
>> >> > in
>> >> > each column is the value of each Booleen field.

Reply to this Message

Nick - 10 Dec 2005 04:52 GMT

I am using the equation you sugested but am still gettin an Error in the box.
=DSum("iif((1)Injury=1,1,0)","Injury1")
Just in case the information I gave you wasincomplete, I look at all this
information and am not sure I have given you enough. The table name is
[Injury1] and the text box attached to the query is [(1)Injury]. I hope this
helps

> Hi,
>
[quoted text clipped - 69 lines]
> >> >> > in
> >> >> > each column is the value of each Booleen field.

Reply to this Message

Michel Walsh - 12 Dec 2005 11:00 GMT

Hi,

Then, try:

=DSum( " iif( [(1)Injury] = 1 , 1, 0 ) " , "Injury1" )

Note that the control name is not as important as the FIELD NAME of the
query. Both can be with the same also.

Hoping it may help,
Vanderghast, Access MVP

>I am using the equation you sugested but am still gettin an Error in the
>box.
[quoted text clipped - 89 lines]
>> >> >> > in
>> >> >> > each column is the value of each Booleen field.

Reply to this Message

Nick - 13 Dec 2005 14:01 GMT

Thanks, I tried your suggestion and still receive an error in the box. I
played with it some and some how found that this equation works
=-sum((1)Injury ="X"). I am not sure why it gives me a negative number, but
by add "-" before the sum the total for are correct. Thanks again

> Hi,
>
[quoted text clipped - 101 lines]
> >> >> >> > in
> >> >> >> > each column is the value of each Booleen field.

Reply to this Message