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.
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
