Report Group Count Woes

[Chrissy]

I'm still learning and need some help.

I have a report that lists clients by source for dates requested. In that
report I have a control with source of "=IIf([Active]=False,"Inactive","") "
to show the inactive clients and blank for actives. This is in the header,
as is all the "detail" so it does not give all the detail items, but one
record for each client.

In the footer, I want to count the number of "Inactive". I have researched
and fail to get it to work. The closest is a text box in the footer with a
source of "=Count(IIf([Active]=False,0))".

This does return correctly the count of all invoices for inactive clients.
I want to return a count of only the clients that are inactive - like my
report shows.

I have tried one text box in detail, source=1 and RunningSumGroup, then
referencing that text box in the footer to no avail.

Are there any suggestions? It is a puzzlement!

 

[Jerry Whittle]

Your IIf inside the Count is missing an argument. If Active = False, it will
return a 0 (as that is a true statement - confuising!). However you don't say
what happens if it is True. You may need a ,1 in there.

"=Count(IIf([Active]=False,1,0))".

Also Count just counts the number of records. You might want Sum. That way
you could add up all the 1's.

"=Sum(IIf([Active]=False,1,0))".

 

[Jerry Whittle]

It just dawned on me that Active is a Yes/No field. Yes = -1 and No = 0 in
Access.

Sum([Active] +1)

That will bump the Yes to 0 and No to 1. Sum will do the math.

 

[Chrissy]

Thanks, Jerry. No joy. Let me clarify...


In a source grouping there are 12 invoices for 8 clients (some have more
than 1 each). All these methods return 12. It is easy for me to do that.
However, what I need to return is 8, the count (without dupes) of the clients
that are inactive.

My query details the invoices for another reason, so these methods will
always return 12.

How do I return only 8?

Thanks.


--
Chrissy

It just dawned on me that Active is a Yes/No field. Yes = -1 and No = 0 in
Access.

Sum([Active] +1)

That will bump the Yes to 0 and No to 1. Sum will do the math.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm still learning and need some help.

I have a report that lists clients by source for dates requested. In that
report I have a control with source of "=IIf([Active]=False,"Inactive","") "
to show the inactive clients and blank for actives. This is in the header,
as is all the "detail" so it does not give all the detail items, but one
record for each client.

In the footer, I want to count the number of "Inactive". I have researched
and fail to get it to work. The closest is a text box in the footer with a
source of "=Count(IIf([Active]=False,0))".

This does return correctly the count of all invoices for inactive clients.
I want to return a count of only the clients that are inactive - like my
report shows.

I have tried one text box in detail, source=1 and RunningSumGroup, then
referencing that text box in the footer to no avail.

Are there any suggestions? It is a puzzlement!

 

[Chrissy]

More...

The report shows only the 8 clients...not the 12 client/invoices.


--
Chrissy

It just dawned on me that Active is a Yes/No field. Yes = -1 and No = 0 in
Access.

Sum([Active] +1)

That will bump the Yes to 0 and No to 1. Sum will do the math.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm still learning and need some help.

I have a report that lists clients by source for dates requested. In that
report I have a control with source of "=IIf([Active]=False,"Inactive","") "
to show the inactive clients and blank for actives. This is in the header,
as is all the "detail" so it does not give all the detail items, but one
record for each client.

In the footer, I want to count the number of "Inactive". I have researched
and fail to get it to work. The closest is a text box in the footer with a
source of "=Count(IIf([Active]=False,0))".

This does return correctly the count of all invoices for inactive clients.
I want to return a count of only the clients that are inactive - like my
report shows.

I have tried one text box in detail, source=1 and RunningSumGroup, then
referencing that text box in the footer to no avail.

Are there any suggestions? It is a puzzlement!

 

[Paul Shapiro]

It sounds like you want something like count(Distinct clientID), which is
part of standard SQL but unfortunately is not supported by Access. You might
be able to get the result by adding an unbound textbox in the client group
header with it's control source set to:
= iif([Active], 0, 1)
and then use the sum of that calculated control. But I seem to remember
Access does not let you perform aggregations, like sum, on a calculated
control. In that case you could use VBA code in the client groupFormat event
to calculate the inactive count.

Thanks, Jerry. No joy. Let me clarify...


In a source grouping there are 12 invoices for 8 clients (some have more
than 1 each). All these methods return 12. It is easy for me to do that.
However, what I need to return is 8, the count (without dupes) of the
clients
that are inactive.

My query details the invoices for another reason, so these methods will
always return 12.

How do I return only 8?

Thanks.


--
Chrissy

It just dawned on me that Active is a Yes/No field. Yes = -1 and No = 0
in
Access.

Sum([Active] +1)

That will bump the Yes to 0 and No to 1. Sum will do the math.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm still learning and need some help.

I have a report that lists clients by source for dates requested. In
that
report I have a control with source of
"=IIf([Active]=False,"Inactive","") "
to show the inactive clients and blank for actives. This is in the
header,
as is all the "detail" so it does not give all the detail items, but
one
record for each client.

In the footer, I want to count the number of "Inactive". I have
researched
and fail to get it to work. The closest is a text box in the footer
with a
source of "=Count(IIf([Active]=False,0))".

This does return correctly the count of all invoices for inactive
clients.
I want to return a count of only the clients that are inactive - like
my
report shows.

I have tried one text box in detail, source=1 and RunningSumGroup, then
referencing that text box in the footer to no avail.

 

[Chrissy]

Thanks.

OK, so I understand I can do it with VBA in the group format. But how?

I am very confused with this.

--
Chrissy

It sounds like you want something like count(Distinct clientID), which is
part of standard SQL but unfortunately is not supported by Access. You might
be able to get the result by adding an unbound textbox in the client group
header with it's control source set to:
= iif([Active], 0, 1)
and then use the sum of that calculated control. But I seem to remember
Access does not let you perform aggregations, like sum, on a calculated
control. In that case you could use VBA code in the client groupFormat event
to calculate the inactive count.

Thanks, Jerry. No joy. Let me clarify...


In a source grouping there are 12 invoices for 8 clients (some have more
than 1 each). All these methods return 12. It is easy for me to do that.
However, what I need to return is 8, the count (without dupes) of the
clients
that are inactive.

My query details the invoices for another reason, so these methods will
always return 12.

How do I return only 8?

Thanks.


--
Chrissy

It just dawned on me that Active is a Yes/No field. Yes = -1 and No = 0
in
Access.

Sum([Active] +1)

That will bump the Yes to 0 and No to 1. Sum will do the math.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I'm still learning and need some help.

I have a report that lists clients by source for dates requested. In
that
report I have a control with source of
"=IIf([Active]=False,"Inactive","") "
to show the inactive clients and blank for actives. This is in the
header,
as is all the "detail" so it does not give all the detail items, but
one
record for each client.

In the footer, I want to count the number of "Inactive". I have
researched
and fail to get it to work. The closest is a text box in the footer
with a
source of "=Count(IIf([Active]=False,0))".

This does return correctly the count of all invoices for inactive
clients.
I want to return a count of only the clients that are inactive - like
my
report shows.

I have tried one text box in detail, source=1 and RunningSumGroup, then
referencing that text box in the footer to no avail.

.