1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 Query: Return count of "Yes" and "No" values for Analyst/Report M

Discussion in 'Business Applications' started by Pyrofeline, Jan 22, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Pyrofeline

    Pyrofeline Thread Starter

    Joined:
    Jan 22, 2015
    Messages:
    9
    I recently built a call log database in Access 2007 for my team to use when calling agents on our top errors we see from a vendor's error report. My boss is requesting a report to see how many items have been resolved

    I have a query started and I am trying to complete this task in a single query (if possible) instead of several queries (as I've done for a couple other things).

    I have 3 tables I'm using: Assignment, AgtInfo and CallLog
    Joins as follows: Assignment.st=AgtInfo.agcy_st
    AgtInfo.agt_id=CallLog.agt_id

    Fields in the results: Assignment.assigned_analyst, CallLog.rpt_mnth, CallLog.resolved

    Resolved is a yes/no field. I've done a query that converts the -1 to a 1, so that the sum is a positive number for the "Yes" values. I'm not sure how to enter the expression to subract the "Yes" values from the total number of values (yes/no) to get to my "No" count.

    Essentially, I'd like the results to display as:

    assigned_analyst, rpt_mnth, CountYes_resolved, CountNo_resolved

    Somehow the connection is just not clicking in my brain with everything I've read though searches. Any help is appreciated! Thanks!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    It is easier to add another column to counf the Nos, use
    0 or is null
    as the IIf statement and use a 1 to count them
     
  3. Pyrofeline

    Pyrofeline Thread Starter

    Joined:
    Jan 22, 2015
    Messages:
    9
    Thank you OBP, Are you talking about adding another column to the table?
     
  4. Pyrofeline

    Pyrofeline Thread Starter

    Joined:
    Jan 22, 2015
    Messages:
    9
    Nevermind, I just figured it out - the coffee hasn't kicked in yet. :) Thank you!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1141703

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice