Unanswered: yes/no fields: how to total or summarize...
New to this forum and to databases:
I created a rather basic MS Access form, which allows me to log procedures by clicking checkboxes. Every month I need to submit a total of my procedures and I would like to create a report in order to do this. My problem is that the report (wizard) does not total the number of boxes checked (ie: true's). It just says 'true' where the total number should be. I have tried changing the formulas, but then it just gives me a -1 number. I am aware that yes = 0 and no = -1, but how do I creat a total for the number of times a check box is checked? I also tried using the abs and count features but these did not work. Any input would be appreciated.
[i]I have tried changing the formulas, but then it just gives me a -1 number. I am aware that yes = 0 and no = -1, but how do I creat a total for the number of times a check box is checked? I also tried using the abs and count features but these did not work. Any input would be appreciated.
A boolean field can only be meaningfully "counted," not totaled.
Try this: "group by" the field, and also "count" the same field. That will give you in one query the number of yes responses and the number of no's.
how do i make the reports wizard do this? the only options i see are to sum and do other such calculations. what would a group or count command look like?
Come with me to Query-Land. Open a query and add the table to it. Now click the "Epsilon" (E) key on the toolbar. Notice that a new row appears on the query grid.
Now click on your yes/no field to add it. Notice that it shows up on the grid and the operator is 'group by'. Click to add the field a second time. On this one change the operator to 'count.' Now run the query. Voila!
Build your report on that query. You can expand your query to include the other fields you need. A report built on the query is a simple one, just like a report based on a table, because the query is doing the work.
If you look around, you'll notice that the Report Wizard produces its advanced reports by creating queries on-the-fly to run them. Another approach that's kinda useful in some cases is to let the RW get you close to what you want, then directly edit the query that it produced to get exactly what you want.
1. once to do the grouping on, and again to be the value counted
the sql for this is --
select id, count(id)
group by id
rows that have the same id value form into groups
for a yes/no field, 0 and -1 are the only values, so, those are the groups
then the number of values in each group is counted, for example, 123 0's and 937 -1's
you could alternatively count the number of rows --
select id, count(*)
group by id
(i think you can just overtype this in query design view, in sql view it's a simple text edit, just like in this posting)
count(*) produces the same results unless the id column allows nulls
nulls are a separate group, and furthermore count(id) does not count the null values
2. yes, you can include other columns, and you may or may not wish to count them separately too --
select id, foo, count(id), count(foo)
group by id, foo
the above may or may not give you the results you expect or desire
if there are no nulls, then count(id) will be equal to count(foo) in each group, and both will be the same as count(*) in that group
many people, myself included, find it easier to think just in terms of count(*) --
select id, foo, count(*)
group by id, foo
this will give you the number of combinations of id and foo, disregarding nulls, since count(*) counts rows
if either id or foo allows nulls, all nulls are grouped together, separately for their respective columns, and then count(id) and count(foo) will give their respective totals depending on how many nulls in their columns
don't go away from this thinking you'll just set all your fields to not null, just to avoid thinking about this type of stuff
there may come a day when you will want to know how to count not null values when some rows have nulls, especially in combinations with other columns
okay, as to the question of "wtf with the third value, isn't yes/no sufficient?"
yes, you could have yes/no and no nulls, if you wanted it to be that way, but the question should be raised for every field
perhaps there are cases where in addition to yes/no, you might want some way to represent "maybe, i don't know, could you repeat the question?"
ok, so the advice and guidance here so far have been excellent.
i hope i'm not being too demanding but i need further assistance:
my form has about 20 different fields, each of these are check boxes (each is a different medical procedure).
i have successfully created a query (using the advice above) for one of the fields. i used 'group by' and then 'count' to sum the check boxes for one field. my problem is when i try and include the other fields on the same query, the results are then inaccurate. do i have to create a separate query for each field? this means that i would have to have 20 different queries in order to summarize all my information from one table/form.