Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville, Florida, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting checked boxes (2000)

    My Access 2000 database contains several checkboxes (call them A, B, C, and D) in each record. I need to create a query to show the number of checked As, Bs, Cs, and Ds. I've tried to use the Expression Builder to create the controls, but every attempt results in a different error message. How do I accomplish this?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting checked boxes (2000)

    This will count the "a" column. I think you have to do a seperate query for each column.

    Count the "a's"

    SELECT Count(Table1.a) AS CountOfa
    FROM Table1
    HAVING (((Table1.a)=True));

    Count the "b's"


    SELECT Count(Table1.[img]/forums/images/smilies/cool.gif[/img] AS CountOfb
    FROM Table1
    HAVING (((Table1.[img]/forums/images/smilies/cool.gif[/img]=True));



    You could then combine them in to one query like this:

    SELECT Query1.CountOfa, Query2.CountOfb
    FROM Query1, Query2;

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville, Florida, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting checked boxes (2000)

    Thanks for your quick response. I'm sure your suggestions would work . . . if I only knew how to apply them. I assume that I must replace "Table1" with the actual name of the table, and a and b with the actual names of their fields. When I Open a new Query in design view, I can fill in "Field", "Table", "Sort", "Show", Criteria", etc. and I don't know where to begin. The error message I see most often is "Invalid Syntax - You may have entered an operand without an operator."

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting checked boxes (2000)

    Sorry, I didn
    Attached Files Attached Files

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Counting checked boxes (2000)

    Another option similar to the previous result is as follows:

    Assume you have created a table named Table1 with the following parameters,
    ID - Primary Key
    A - Yes / No Check box
    B - Yes / No Check box
    C - Yes / No Check box
    D - Yes / No Check box

    Four records have been entered, A is yes in all four, B is yes in 2-4, C is yes in 3-4, and D is yes in 4. Thus, A has been checked 4 times, B 3 times, C 2 times and D 1 time.

    Create a Query as follows:

    Identify the first query field as an expression: Expr1:Count(*)
    Identify the second query field as the field being counted as follows, for instance to count the quantity of C's that have been checked, Insert C as the field name. In Criteria type Yes. When you run the query, you will get a result of 2 in the EXPR1 field.

    The SQL view will look as follows:

    SELECT Count(*) AS Expr1
    FROM TABLE1
    WHERE (((TABLE1.C)=Yes));

    Good Luck.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville, Florida, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting checked boxes (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Please forgive the length of time this response has taken. Immediately after my last post an opportunity arose to multiply the scale of my business and that has occupied every moment for the last couple of months.

    Things have steadied into the new rhythm now, and I had a chance to work on this query. Your sample made it clear and I am now getting the information I need from the database. I probably did it the hard way - starting from scratch to design a new select query, I could not figure out how to get the "Total:'" row to enter "Count" and "Expression" -- the row just didn't exist. I finally just used your "Query1" as a framework, made the necessary changes, and saved it as a new query. I'm sure there's a less cumbersome way to get there, but this worked and I thank both of you, Rupert & GaryPSwanson, for your help.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting checked boxes (2000)

    You only see the Totals row if you click on the sigma button on the toolbar in the query grid to change your query to a group by query. Once you change the query to a group by, you can use the built in aggregations like Sum, Count, etc. Is that what you were looking for?
    Charlotte

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville, Florida, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting checked boxes (2000)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Bingo! How simple, when you know what you're doing. Thank you. Maybe I should RTFM.

Posting Permissions

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