Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing several Count fields (Access 2003 SR-1)

    HISTORY: I sent out an online web survey to all users at all locations of our company, asking which of several training classes they would like to take. They indicated which classes, by checking a box in front of the class. When they hit Submit, the results were dumped into a database (with a YES entry where they checked a box, and a blank where they did not). Now I'm attemping to analyze that information.

    I have created a qry that will do a count of all users who want to take class A or B or C, and I can specify which location by including the "Facility" field. Doing a count query for how many people want to take each class at a single location is easy enough. I can even do searches on multiple locations by specifying " 'FacilityA' OR 'FacilityB' " in the criteria and it will return a count/total for each class at those specific locations. But, I would like to total all results from FacilityA AND FacilityB - and when I type just that in my Facility Criteria I get bubkus - I guess because it is looking for instances where someone from FacilitiesA and B selected a course, and that will not exist because user's only work in one place or another. Can anyone help me get my syntax right? Better yet, can anyone understand what I'm trying to do ?!?! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

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

    Re: Summing several Count fields (Access 2003 SR-1)

    I'll bite. No, I don't understand what you are trying to do. What do you mean by "someone from Facilities A and B"?

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Summing several Count fields (Access 2003 SR-1)

    I'm with Hans -- not sure I understand completely either -- but have you tried to analyze the results with a Crosstab Query? Sounds like that would help, especially if you include totals.

    Kathi

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing several Count fields (Access 2003 SR-1)

    Hey Hans - thanks for responding, even though you didn't understand. I was going to attach an abbreviated copy of the database, but for some reason even my copy is 2mb. Oh well. For the time being, I've created a workaround by exporting the information into Excel, and just doing an AutoSum on all the count fields to sum up how many users from each of the listed facilities are interested in a certain class. I CAN attach that, if it will give you any better idea of what I needed. If not, don't sweat it since I found a semi solution. Thanks again!
    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: Summing several Count fields (Access 2003 SR-1)

    An easier way to do this instead of using the yes / no field that generates a -1 for yes or 0 for no and requires you to use the count function is to use a numeric field. Put a 1 for yes and 0 for no for the class data. Then you could simply generate a query that gets courses A, B, C, etc., and each location, turn on the grouping feature, and sum on each of the class fields while grouping on location. This is much easier then using the count feature.

    HTH (And that I actually made some sense)
    Regards,

    Gary
    (It's been a while!)

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Summing several Count fields (Access 2003 SR-1)

    I believe you can also SUM the yes/no field based on selection criteria to accomplish the same thing:

    something like "ABS(SUM(myYesNo))" where :myYesNo" is the field/control storing their response.

    HTH

Posting Permissions

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