If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting in a Query
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 |
#2
|
|||
|
|||
Counting in a Query
Try Like "X" and group on
"Susan L" wrote: 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 |
#3
|
|||
|
|||
Counting in a Query
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 Golfinray wrote: Try Like "X" and group on "Susan L" wrote: 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 |
#4
|
|||
|
|||
Counting in a Query
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 "Golfinray" wrote: Try Like "X" and group on "Susan L" wrote: 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 |
#5
|
|||
|
|||
Counting in a Query
Susan L wrote:
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 a -Sum(LO1="X") or Abs(Sum(LO1="X") or Count(IIf(LO1="X",1,Null)) or Sum(IIf(LO1="X",1,0)) -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|