Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    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.

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Re: yes/no fields: how to total or summarize...

    [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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Oct 2003
    Posts
    5

    ...

    ok, understood.
    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?

    thanks again.

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Re: ...


    ok, understood.
    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    awesome!
    thanks so much for this advice...
    now, two questions:

    1: why do i have to have two objects one that says gouped by and one that says count ? why not just use count? i tried to delete the gouped by and it didnt give me an accurate number.

    2: i tried adding other fields to the same query, all next to each other (all with grouped by and then count) but the results were not accurate. do i have to make a seperate query for each field?

    thanks again!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. once to do the grouping on, and again to be the value counted

    the sql for this is --

    select id, count(id)
    from thetable
    group by id

    sample results:
    0 123
    -1 937

    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(*)
    from thetable
    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)
    from thetable
    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(*)
    from thetable
    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?"

    nulls are perfect for that

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Posts
    5

    Talking

    fascinating...
    thanks again

  8. #8
    Join Date
    Oct 2003
    Posts
    5

    Question need more info...

    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.

    thanks,
    ed

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select sum(IIF(isnull(checkbox1),0,1)) as checkbox1count
         , sum(IIF(isnull(checkbox2),0,1)) as checkbox2count
         , sum(IIF(isnull(checkbox3),0,1)) as checkbox3count   
         ...
      from yourtable
    note: no GROUP BY

    there is more than one way to remove the outer integument from a feline...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •