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.

Conditional Counting in Access

Discussion in 'Business Applications' started by krexroth, Nov 3, 2004.

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

    krexroth Thread Starter

    Joined:
    Nov 3, 2004
    Messages:
    3
    I have what I hope is a simple issue in Access. I have a simple set of results of targeted email campaigns. I have a Access report grouped by Salesperson and within that group, each email recipient is listed with an indication of whether or not that person opened our email. Such as:
    Smith, George Opened
    Smith, John Opened
    Smith, Sarah Not Opened.

    I am looking for the proper function/syntax to count the total number of "Opened" messages in each group. I've tried a DCount function, but haven't gotten it to work. I know I can work with a count in a query, but I'm interested to see if I can do this right in the report and learn something new regarding functions.
    Thanks!
     
  2. simcha2u

    simcha2u

    Joined:
    Oct 27, 2004
    Messages:
    16
    Such an operation has to go into the report footer. Right click under the blue bar and hit Report Header/Footer. Then create a control there and set the control source to:

    =Count([NameOfControl])

    forget about dcount, this should work for you.
     
  3. krexroth

    krexroth Thread Starter

    Joined:
    Nov 3, 2004
    Messages:
    3
    Thanks for the reply. My concern though is the conditional part. Just putting a count statement doesn't account for my need to filter out the "Not Opened" values and count only the "Opened" values. It's that specific syntax that I'm looking for.
    Thanks
     
  4. simcha2u

    simcha2u

    Joined:
    Oct 27, 2004
    Messages:
    16
    The problem with using Dcount is that it will gather the information from the Table and not the detail section of your report. You would then be bypassing the filters you have set in the report ie grouping by salesperson. I think an easy way to handle this would be to make a non visable control named StatusCount that is set to:
    =IIf([YourStatusField]="Opened",1,0)
    Make this control a running sum over the group.

    Then either in a group footer or report footer have the control thats =[StatusCount]
     
  5. krexroth

    krexroth Thread Starter

    Joined:
    Nov 3, 2004
    Messages:
    3
    That's exactly what I ended up doing. I was hoping that there was a more "elegant" control I could use that I wasn't aware of. Thanks for your help Simcha2u!
     
  6. Sponsor

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/292022

  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