Total Yes/No Fields 1

[labninja]

I need to do a report of all the Yes/No fields in a table, but only do the totals of the Yes values. So, lets say I have a table with the following fields: Date, Dispatched and Delivered (the latter two are Yes/No fields) - I want to do a report over a date range, with total dispatched and total delivered. I would appreciate it if anybody can help me with this, maybe e-mail me an example.<br><br>Kind regards

 

[Amiel]

Try dropping these guys in a query.<br><br>'get the total delivered with<br>&quot;SELECT Count(*) where Delivered = -1&quot;<br><br>then...<br>'get the total dispatched with<br>&quot;SELECT Count(*) where Dispatched = -1&quot;<br><br>...or<br>&quot;SELECT Count(*) where Dispatched = -1 and Delivered = -1&quot;<br>get the total dispatched & delivered.<br><br>...or even...<br>&quot;SELECT Count(*) where Dispatched = -1 and Delivered = 0&quot;<br>get the total dispatched but not delivered !<br><br><br>Have fun lab ninja.<br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[CrimsonDiva]

Hey Labninja,

Did you get your problem solved? I ran into the same problem, but when I tried to total my yes/no fields I come up with garbage data. Help!

Crimson Diva

 

[rafe]

A variant on Amiel's solution is to use the DCount() function. Really a mini-query thay you can use as a control source, if properly formated, or in VBA code.

Something like:

= DCount(&quot;[Date]&quot;, &quot;MyTable&quot;, &quot;[Dispatched] = True AND [Date] > #&quot; & FromDate & &quot;# AND [Date] < #&quot; & ToDate & &quot;#&quot

 

[MichaelRed]

I often use the Group/Sum type of query to get the &quot;count&quot; 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

 

[Quehay]

Another simplistic option, good for those still limited to the QBE interface, is to put the same field in twice in a summary query, the first column is &quot;Count&quot; and the second is &quot;Where&quot; with &quot;Yes&quot; in the criterion field. This uses aliases, which are the names given to recordset columns by putting them in front of the field name followed by a colon (at least in the graphic context).

tbl_RandomTexts
RndmID
RndTitle
RndSold

Query:

ID | Cnt: RndSold | Sort: RndSold
WHERE:
GroupBy &quot;Yes&quot;

Having the ID in there would give a total for each member. Just having the field counted with no &quot;group by&quot; field will give an overall count. **Remember that you can build these totals and them reference them in subsequent queries by adding them and just bringing the countofwhatever field into the new query. You can do some really sophisticated queries without know the first SQL keyword (although it helps).

Good luck

 

[Schnauzer]

I am having the same issues as labninja was having. Except I have a report that already isolated the dates.

I used the:
=Abs(Dsum(&quot;[columnname]&quot;,&quot;[tblName]&quot)
and received the total &quot;Yes&quot; for the entire column, not for the specific date ranges.

What else can I do to isolated the date ranges?