Counting Query

[Yakman]

I have a table with five fields, Mon, Tue, Wed, Thu and Fri.
Each record for each field is either a "Yes" or "No"
ie
Mon Tue Wed Thu Fri
Yes Yes No Yes No
Yes Yes Yes Yes Yes
No No Yes No Yes
Yes Yes No Yes No
No Yes Yes Yes No

I need a query to count the number of "No"'s, in each column.

I can count 1 column, but no more. I have had to make 5 queries, 1 for each
day.

There must be a better way.

 

[John Spencer (MVP)]

Try the following.

SELECT Abs(Sum([Mon]=False)) as MonCount,
Abs(Sum([Tue]=False)) as TueCount,
Abs(Sum([Wed]=False)) as WedCount,
Abs(Sum([Thu]=False)) as ThuCount,
Abs(Sum([Fri]=False)) as FriCount
FROM YourTable

IF you are using the query grid then you would need to do the following for each field.

Field: MonCount: Abs(Sum([Mon]=False))
Total: Expression

 

[PC Datasheet]

Your problem is caused by the table not being designed correctly! If
possible, you should first change the design of the table to:

TblDayYesNo
DayYesNoID
DayOyWeek
YesNo

Your data would then look like:
1 Mon Yes
2 Mon Yes
3 Mon No
4 Mon Yes
5 Mon No
6 Tue Yes
7 Tue Yes
8 Tue No
9 Tue Yes
10 Tue Yes
etc

Getting a count of No's for each day is then simple---

Create a query based on the table. Pull down the DayOfWeek and YesMo fields.
Set the criteria of the YesNo Field to No. Click on the Sigma(looks like E)
button on the toolbar at the top of the screen. Under YesNo change Group By
to Count. When you run the query you will get the number of No's for each
DayOfWeek.

 

[Dale Fye]

Although he didn't mention it, he probably has other fields in his original
table (week, person, etc) which would also be needed to define his primary
key, along with the DayOfWeek field you describe.

Dale

 

[PC Datasheet]

Yes, there may be other fields but they are not part of the primary key in
the table I propose. The primary key is DayYesNoID, an autonumber field.
DayOfWeek is just one of the many fields that may be in the table.

Steve
PC Datasheet

 

[Yakman]

Thank you very much, a little tweak and your solution worked fine.

Yakman

Try the following.

SELECT Abs(Sum([Mon]=False)) as MonCount,
Abs(Sum([Tue]=False)) as TueCount,
Abs(Sum([Wed]=False)) as WedCount,
Abs(Sum([Thu]=False)) as ThuCount,
Abs(Sum([Fri]=False)) as FriCount
FROM YourTable

IF you are using the query grid then you would need to do the following for each field.

Field: MonCount: Abs(Sum([Mon]=False))
Total: Expression

I have a table with five fields, Mon, Tue, Wed, Thu and Fri.
Each record for each field is either a "Yes" or "No"
ie
Mon Tue Wed Thu Fri
Yes Yes No Yes No
Yes Yes Yes Yes Yes
No No Yes No Yes
Yes Yes No Yes No
No Yes Yes Yes No

I need a query to count the number of "No"'s, in each column.

I can count 1 column, but no more. I have had to make 5 queries, 1 for each
day.

There must be a better way.