cpunting yes/no

[mrk2]

How can I count the number of unchecked or checked yes/no entries from a table in a query? when I use the "count" feature it shows no data in the query?

 

[jjonesal]

If I'm understaing correctly, you must use a group by query like:

SELECT TableA.myyesno, Count(TableA.myyesno) AS CountOfmyyesno
FROM TableA
GROUP BY TableA.myyesno;


J. Jones
jjonesal@cableone.net

 

[mrk2]

ok here is what I got from your post. Both the table and the cloumn are called attendance, which probably isnt the smartst thing in the world. Where does this code go, I'm not sure what to do with this?

SELECT Tableattendance.attendance, Count(attendance.attendance) AS CountOfattendance

 

[jjonesal]

If you are saying that the Attendance column in your Attendance table is a Yes/No then:

SELECT Attendance.Attendace, Count(Attendance.Attendance) AS CountOfAttendace
FROM Attendance
GROUP BY Attendance.Attendance;

Create a new query using the design view. Select View, SQL from the menu at the top and copy and paste the above code. Then, you can execute the query or go back to Design view (View, Design View) to see the graphical representation of the query.


J. Jones
jjonesal@cableone.net

 

[mrk2]

ok I need to add a twist to this, this data needs to be sorted by another data element in that table. Do I write
GROUP BY Attendance.ID;
rather than
GROUP BY Attendance.Attendance;

???????????

 

[jjonesal]

No, you use an ORDER BY clause to do the sorting. J. Jones
jjonesal@cableone.net

 

[mrk2]

and an ORDER BY clause looks like....?

 

[jjonesal]

ORDER BY Attendance.ID


An ORDER BY clause simply lists all fields on which you want to sort and comes after the GROUP BY.

ORDER BY a, b, c

would sort first by field a, then within that by field b, then within that b field c. An ascending sort is the default, but you could include DESC after a field to indicate that the sort should be descending.

ORDER BY a, b DESC, c

would sort first ascending by a, then within that descending by b, and then within that ascending by c

J. Jones
jjonesal@cableone.net

 

[CosmoKramer]

Add two expression fields to your query:

CountYes: DCount("[yesnofield]","yourtable","[yesnofield]=Yes")

CountNo: DCount("[yesnofield]","yourtable","[yesnofield]=No")

 

[MichaelRed]

Using aggregate queries (particularly COUNT) obviates the use of order by. It (the aggregate "COUNT" will only return the single record, so ordering is 'moot'.

Further, the Count (query) -as shown- will simply show the number of records, as the boolean (Yes / No) field will always be either &quot;True&quot; ( <> 0) or &quot;False&quot; ( = 0). Depending on the 'real' need, you can count the &quot;true&quot; (or false) entries by including a Where clause for the field.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mrk2]

What is wrong with the following Order BY clause???

SELECT Attendance.attendance AS Expr1, Count(Attendance.attendance) AS CountOfAttendance
FROM Attendance
GROUP BY Attendance.attendance
ORDER BY Attendance.ID;

 

[MichaelRed]

see previous post. How many 'results' do you get? then think about how it even CAN be ordered.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places