Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Question Unanswered: how to count multiple fileds in access

    my query is
    i have a database which have four columns and in each column there is value "Yes" or "No". i want to calculate all the "yes" from all the fields and similarly all the "No" for a perticular name.

    Field name:
    name Good Very good Poor

    satpal yes yes No
    Partap No No Yes
    Parminder Yes yes no
    Satpal yes No No

    from this type of database

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by satpal_singh777 View Post
    my query is
    i have a database which have four columns
    It's a table that has columns, not a database. Moreover the design seems odd and I'm pretty sure it's not normalized.

    Also, you should not used reserved words (such a "name") or non alpha-numeric characters (such as a space: "Very good") for naming objects (tables, columns, indexes, etc.) in an Access database.

    Anyway, here's a solution. As you did not specified the name of the table, it's "Table1" in my example:
    Code:
    SELECT [name], 
           Abs(SGood) + Abs(SVerygood) + Abs(SPoor) AS CountYes, 
           (CTotal * 3) + SGood + SVerygood + SPoor AS CountNo
    FROM (
           SELECT Table1.[name], 
                  Sum(Table1.Good) AS SGood, 
                  Sum(Table1.[Very good]) AS SVerygood, 
                  Sum(Table1.Poor) AS SPoor, 
                  Count (*) AS CTotal
           FROM Table1
           GROUP BY Table1.[name]
         )
    ;
    You can add:
    Code:
     WHERE [name] = 'satpal'
    before the final semicolon if you want the result for only one value of [name].
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    actually m working wt MS Access 2002
    i am giving you some more detail regarding the query

    there is a questionaire containig six questions each question will be marked Yes or No. i want to implemented it on a certen set of teachers regarding their feed back by the students based on these six question. i want to calculate total no. of Yes and no at once for six question for one teachers


    Table : Evaluation

    Fields are as follows

    Name class Q1 Q2 Q3 Q4
    Satpal BCA Yes Yes Yes Yes
    Satpal BCA NO Yes No Yes
    Navdeep MCA No Yes No Yes


    i want to get a query will calculate total yes and no of perticular teacher
    say in this example Navdeep is Having 2 Yes and 2 No the same way this is condition with one record. there is posibility of multiple records with same name. how to calculate that as there is satpal in above example

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ypu coudl do this with an IIF
    eg
    SELECT class,
    COUNT(IIF(Q1=TRUE,1,0) as Q1Yes,
    COUNT(IIF(Q2=TRUE,1,0) as Q2Yes,
    COUNT(IIF(Q3=TRUE,1,0) as Q3Yes,
    COUNT(IIF(Q4=TRUE,1,0) as Q4Yes,
    COUNT(Q1) as NoRepsonses
    FROM mytable
    GROUP BY class
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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