Counting in a Query

[Susan L]

I've searched, but did not find an answer.

I am attempting to have a query count the number of "X"s in 6 fields ([LO1],
[CSI], etc.) in another query. These values pertain to US states, and the
states are repeated numerous times, with varying values in the 6 fields.
Values are either "X" or blank.

I tried a Totals query, grouping on the [StateName]. I then selected Count
in each of the 6 fields. When I ran the query, The states grouped properly.
but each of the 6 fields had a value of 53. I'm sure the answer is simple,
but I'm stumped.

 

[Golfinray]

Try Like "X" and group on

 

[John Spencer]

Probably the fields are storing "X" or a zero-length string "". Since you did
not post the SQL statement you are attempting to use it is difficult to
provide any advice (Hint: in design view, View: SQL from the menu)

Try changing

Field: CSI
Total: Count

to
Field: Abs(CSI="X")
Total: Sum

or
Field: IIF(CSI="X",CSI,Null)
Total: Count

Count counts the values that are not null. A zero-length string is not null
and X is not null, so you might as well count the rows.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Try Like "X" and group on

I've searched, but did not find an answer.

I am attempting to have a query count the number of "X"s in 6 fields ([LO1],
[CSI], etc.) in another query. These values pertain to US states, and the
states are repeated numerous times, with varying values in the 6 fields.
Values are either "X" or blank.

I tried a Totals query, grouping on the [StateName]. I then selected Count
in each of the 6 fields. When I ran the query, The states grouped properly.
but each of the 6 fields had a value of 53. I'm sure the answer is simple,
but I'm stumped.

 

[Susan L]

Am a little dense today -- not sure where to put the Like X -- and are you
saying group on each of the 6 fields, essentially all the fields in the
query? (I have figured out that the 53 equals the number of rows once the
State field is grouped (FYI: one territory is included).
Here are the fields:
State StateCode LO1 CSI MSC ENF PAT ENF
GroupOn GroupOn Count
--
susan

Try Like "X" and group on

I've searched, but did not find an answer.

I am attempting to have a query count the number of "X"s in 6 fields ([LO1],
[CSI], etc.) in another query. These values pertain to US states, and the
states are repeated numerous times, with varying values in the 6 fields.
Values are either "X" or blank.

I tried a Totals query, grouping on the [StateName]. I then selected Count
in each of the 6 fields. When I ran the query, The states grouped properly.
but each of the 6 fields had a value of 53. I'm sure the answer is simple,
but I'm stumped.

 

[Marshall Barton]

I've searched, but did not find an answer.

I am attempting to have a query count the number of "X"s in 6 fields ([LO1],
[CSI], etc.) in another query. These values pertain to US states, and the
states are repeated numerous times, with varying values in the 6 fields.
Values are either "X" or blank.

I tried a Totals query, grouping on the [StateName]. I then selected Count
in each of the 6 fields. When I ran the query, The states grouped properly.
but each of the 6 fields had a value of 53. I'm sure the answer is simple,
but I'm stumped.

Count counts any non-Null values so, it depends on the type
and values in the fields. If you used Count(LO1) and LO1 is
a YesNo field then there will not ba any Null values so
every record will be counted. The same may be true for a
Text field that has its AllowZeroLength property set to Yes.

For YesNo fields, you can count the X values by using any
number of expressions like:

-Sum(LO1)
or
Abs(Sum(LO1)
or
Count(IIf(LO1,1,Null))
or
Sum(IIf(LO1,1,0))

The corresponding expressions for Text fields are:

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