Totaling yes/no Fields for a Report

[CrimsonDiva]

Hello,

Here's the scenario:
I have a table with 11 yes/no fields that are used to hold meeting attendance for a particular month of the year. I would like a total "YES" count for EACH of these fields, and I want to show the results in a Report. I've tried several queries, but I'm doing something wrong.

Example Fields: [May 00], [Jun 00], [Jul 00]
Table Name: MeetingInfo

Can someone explain EXACTLY how to word my query (either in SQL or in DesignView). Please include the information that should be on each line (of Query DesignView):
Field
Table
Total
Show
Criteria

FYI: I am working with Access 2000. Thanks so much!

 

[Rohdem]

Place this code in a module:

Function RCount(ParamArray FieldValues()) As Variant
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If (varArg) Then
lngCount = lngCount + 1
End If
Next
RCount = lngCount
End Function

Now make a query that has this in the field line:

meetingcount: rcount([May 00], [June 00], [Jul 00])

The only bad thing about this is you have to list all your field names in your function call, but it works, I tested it!


Mike Rohde
rohdem@marshallengines.com

 

[MichaelRed]

Ther is another thread discussing the counting of Yes/No fields in one of the other forums. It has several other soloutions (including my favorite, group/sum with abs - to get the values to be positive). If I knew how to capture the URL to paset it here, I would - but I can't figure out how "they" do that. I'm pretty sure it is in the Ms. Access General discussion, but it might be the queries one.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

O.K. I couldn't resist. I went and copied my favorite soloution from the Ms. Access General forum:

Dec 13, 2000
I often use the Group/Sum type of query to get the "count" of several Yes/No fileds at the same time. Of course, when you sum a yes no field, you always get the count of the yeses as a negative number, so niceity compells you to then either tane the negative of this or it's absoloute value, but the actual count of several fields can be done in a single query - very straight-forward.

Source Table:
MyDate Dispached Delivered
11/20/99 Yes Yes
11/21/99 Yes Yes
11/22/99 Yes No
11/23/99 No Yes
11/24/99 No No
11/25/99 Yes No
11/26/99 Yes No
11/27/99 Yes No
11/28/99 Yes No
11/29/99 Yes No
11/30/99 Yes No
12/1/99 Yes No
12/2/99 No Yes
12/3/99 Yes No
12/4/99 Yes Yes
12/5/99 Yes No
12/6/99 Yes No
12/7/99 Yes Yes


MyQuery SQL:
SELECT Sum(Abs([Delivered])) AS Delv, Sum(Abs([Dispached])) AS Disp
FROM tblSumYseNo
WHERE (((tblSumYseNo.MyDate) Between #11/21/99# And #12/6/99#))
GROUP BY 12;

Results:
Delv Disp
4 13


Note that I provided a field in the query for the Group By clause as a constant, so all records are in the group - only the where clause (date range) selects the rercords. I placed the dates in the query itself for illustration purposes, in a real solution these would, of course, be parameters and probably fed from a form.

I realize this is NOT EXACTLY the format you asked the soloution to be in. If you create the table [tblSumYseNo] as shown above, copy the query text into a query SQL view and then switch to the design view, you should be able to "de-code"/transform to the format you want.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[CrimsonDiva]

Hey MichaelRed,

I tried your solution and it worked! Now I just have to get these query results into my report and I'm done. Thanks so much for your help. |-0

Crimson Diva