Counting records in a report

[John]

Im going crazy with this and hope someone can help...

I have a report that is grouped by a Key #, within each
unique key# there are a list of possible accounts (each
having a 2 letter acronym) then there is a status field
for each of those accounts stating whether it is opened or
closed. It Looks something like this

Key Type Status
100
X
Open
Open
Closed
Y
Open
Sum of X: ?
101
X
Closed
Y
Open
Open
Sum of X: ?

I am trying to get a way to get a count of Open or Closed
accounts for X for each unique Key. Any thoughts? I can
get the count right now to count all of the status's so
for key 100 the 'Sum of X: ' is 4 and for key 101 it is
3. but i can't get the real answer which should be 3 and
1 respectively to work correctly.

I really appreciate your help. thanks

 

[Les]

Create a field on your detail line (the one that prints
status). It will be an expression =iif([type]="x",1,0).
Then, in your group footer (where you write sum of x),
have a field that is set to count([field in detail]).

 

[Marshall Barton]

Im going crazy with this and hope someone can help...

I have a report that is grouped by a Key #, within each
unique key# there are a list of possible accounts (each
having a 2 letter acronym) then there is a status field
for each of those accounts stating whether it is opened or
closed. It Looks something like this

Key Type Status
100
X
Open
Open
Closed
Y
Open
Sum of X: ?
101
X
Closed
Y
Open
Open
Sum of X: ?

I am trying to get a way to get a count of Open or Closed
accounts for X for each unique Key. Any thoughts? I can
get the count right now to count all of the status's so
for key 100 the 'Sum of X: ' is 4 and for key 101 it is
3. but i can't get the real answer which should be 3 and
1 respectively to work correctly.

Instead of using =Count(*), use one of these:

=Count(IIf(Account = "X", 1, Null))
or
=Sum(IIf(Account = "X", 1, 0))
or
=Abs(Sum(Account = "X"))