Results 1 to 2 of 2
  1. #1
    RCamper is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013

    Count function to summarize multiple fields in one query


    Here is an example of my data:

    Field1 Field2 Field3
    Certain Certain Possible
    Probable Unsure Certain
    Certain Possible Probable

    I want to summarize it to look like this:
    Certainty Field1 Field2 Field3
    Certain 2 1 1
    Probable 1 0 1
    Possible 0 1 1
    Unsure 0 1 0

    In other words, I would like to get a count of each text occurrence for each field.

    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Data structure is not normalized. If it were, could use a CROSSTAB query.

    How many 'certainty' values are there - only those 4? Try a UNION query. No designer or builder for UNION, must type (or copy/paste) into SQL View window of query designer.

    SELECT "Certain" As Certainty, Sum(IIf(Field1="Certain",1,0)) As F1, Sum(IIf(Field2="Certain",1,0)) As F2, Sum(IIf(Field3="Certain",1,0) )As F3 FROM tablename
    UNION SELECT "Probable", Sum(IIf(Field1="Probable",1,0)), Sum(IIf(Field2="Probable",1,0)), Sum(IIf(Field3="Probable",1,0)) FROM tablename
    UNION SELECT "Possible", Sum(IIf(Field1="Possible",1,0)), Sum(IIf(Field2="Possible",1,0)), Sum(IIf(Field3="Possible",1,0)) FROM tablename
    UNION SELECT "Unsure", Sum(IIf(Field1="Unsure",1,0)), Sum(IIf(Field2="Unsure",1,0)), Sum(IIf(Field3="Unsure",1,0)) FROM tablename;
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 25
    Last Post: 10-17-2012, 01:51 PM
  2. Replies: 8
    Last Post: 09-26-2012, 01:51 PM
  3. Multiple count of query fields
    By BLD21 in forum Queries
    Replies: 2
    Last Post: 05-23-2011, 01:09 PM
  4. Update query to summarize multiple values
    By ser01 in forum Queries
    Replies: 3
    Last Post: 05-15-2010, 09:38 AM
  5. Count function on query
    By yousillygoose in forum Queries
    Replies: 1
    Last Post: 02-15-2010, 09:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums