Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Total Query

Discussion in 'Information Technology' started by ferde, Jul 28, 2006.

Tags:
  1. ferde

    ferde
    Expand Collapse
    Guest

    I am Brand NEW at Access and am having difficulty just trying to add the
    number of Yes responses I'm getting in a field. I am using the "Total"
    feature in a query. I have one field in my table called "Stroke" and it has
    check boxes indicating yes or no. I would like to add the number of yes
    boxes in the stroke field so I can get a "TOTAL" number of yes responses
    using the design query . When I use the "sum" feature it gives me a -1 by
    each yes entry in the view mode. When I use count it counts all the records
    in the whole Stroke field.

    Please help a newbie if you can
     
  2. Loading...


  3. JPC

    JPC
    Expand Collapse
    Guest

    Add the Yes/No field to your query grid again. In the Total: area, select
    Where. In the Criteria: area, enter =Yes. And UnCheck the Show box.

    "ferde" wrote:

    > I am Brand NEW at Access and am having difficulty just trying to add the
    > number of Yes responses I'm getting in a field. I am using the "Total"
    > feature in a query. I have one field in my table called "Stroke" and it has
    > check boxes indicating yes or no. I would like to add the number of yes
    > boxes in the stroke field so I can get a "TOTAL" number of yes responses
    > using the design query . When I use the "sum" feature it gives me a -1 by
    > each yes entry in the view mode. When I use count it counts all the records
    > in the whole Stroke field.
    >
    > Please help a newbie if you can
     
  4. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    The values of Yes/No fields are Boolean TRUE or FALSE, which are implemented
    in Access as -1 or 0. Its bad programming practice to rely on the
    implementation, however, and one should always work with the Boolean values
    (you'll sometimes see this done by summing the absolute value of the field,
    which is an example of this frowned upon practice; its what the head of a
    software company of my acquaintance once called 'being unduly chummy with the
    implementation').

    To count the TRUE values you can SUM the return value of an expression which
    returns 1 or 0 depending on whether the value of the field is TRUE or FALSE.
    The IIF function does this so you can use that. By summing the return values
    of all rows you are in effect counting all those which are TRUE because they
    return 1 while the FALSE rows return zero.

    In query design view create a Totals query in the usual way and in the
    'field' row of a blank column enter:

    CountOfStroke:SUM(IIF([Stroke],1,0))

    In the totals row select SUM.

    However, I'm a little concerned that you might be using a Boolean (Yes/No)
    field here inappropriately and be guilty of what's called in the jargon
    'encoding data as a column heading'. If you'd care to explain in a little
    more detail what your table represents and the role of this field in it I can
    probably advise further.

    Ken Sheridan
    Stafford, England

    "ferde" wrote:

    > I am Brand NEW at Access and am having difficulty just trying to add the
    > number of Yes responses I'm getting in a field. I am using the "Total"
    > feature in a query. I have one field in my table called "Stroke" and it has
    > check boxes indicating yes or no. I would like to add the number of yes
    > boxes in the stroke field so I can get a "TOTAL" number of yes responses
    > using the design query . When I use the "sum" feature it gives me a -1 by
    > each yes entry in the view mode. When I use count it counts all the records
    > in the whole Stroke field.
    >
    > Please help a newbie if you can
     

Share This Page