Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting yes values (Access XP)

    I have a table with one autonumber field, one text field and 60 yes/no fields to track objectives complete. For each record I need a count of yes values and a count of no values. Any ideas on how to count yes values in a single record? I have thought on it all last week and could not come up with any ideas.

    Carla

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting yes values (Access XP)

    This is a very unfortunate table design. Instead of 60 Yes/No fields, you should have a separate table with an ID field (linked to the AutoNumber field in your table), an Objective ID field and one Yes/No field. You could then use a simple group by query to count the number of Yes values.
    In the table as is, you're forced to do something like:

    CountYes: Abs([Field1]+[Field2]+[Field3]+...+[Field60])

    CountNo: 60-[CountYes]

  3. #3
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting yes values (Access XP)

    I agreeit is a poor table design, I am thinking a redesign might be the best solution. I thought I might look at any ideas to get the information I need, but you have just confirmed I might as well just get to fixing the real problem.

    As always thank you,
    Carla

  4. #4
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting yes values (Access XP)

    Hans,

    Just as a followup I spent the time to redesign the existing database and queried out all the old data into a new structure. The original structure was built long before my time and I am always reluctant to change what has already been done, even if I think the structure and design are poor. The result is even better than I would have thought and well worth the time spent. I can now display a screen with all the yes values and the record count gives a very easy to view answer of total yes values.

    Carla

Posting Permissions

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