Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010

    Unhappy Multiple Count Ifs and Expressions in Query


    I am trying to write a query in query design to take a count on electronic invoices vs. manual invoices based on 2 fields in a table. Field 1: Doc Type. Field 2: User Type.

    For electronic, I'd like to create some sort of expression that will Count all Doc Types that start with an "I" but also all doc types that start with an "R" but also have a User Type that start with a "Z".

    For manual, I just need to count all Doc Types that are not "I" but also Doc Types those that start with "R", but do not have a User Type that starts with "Z". Any help would be huge, I'm very lost.


    Elect = Doc Types: "I*", "R*" (User Type"Z*")
    Manual = Doc Types: Not "I", but can be "R*" (Not User type "Z*").

    Hopefully that is clear. Thanks in advance.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Dayton, OH
    I would switch to SQL view and just add the criteria there (you'll need to add WHERE in SQL view) . I find it kind of cumbersome to add criteria like this in design grid view, but that is just me.

    For the electronic, the criteria would be something like this:

    WHERE ([Doc Types] LIKE "I*" AND [User Type] LIKE "Z*") OR ([Doc Types] LIKE "R*" AND [User Type] LIKE "Z*")

    For the manual, are there any other choices other than I* and R*? If not, then this should work:

    WHERE ([Doc Types] LIKE "I*" AND [User Type] NOT LIKE "Z*")

    If there are other choices the this:

    WHERE ([Doc Types] NOT LIKE "R*" AND [User Type] NOT LIKE "Z*")

    BTW you will need 2 separate queries, one for each type

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

Similar Threads

  1. Nested If Expressions
    By Lynn in forum Forms
    Replies: 5
    Last Post: 03-25-2010, 10:11 AM
  2. Multiple count query
    By aajay05 in forum Queries
    Replies: 2
    Last Post: 03-08-2010, 10:00 AM
  3. Conditions / Expressions
    By Mark344 in forum Access
    Replies: 1
    Last Post: 02-19-2010, 08:15 AM
  4. using COUNT for multiple tables
    By kwalt in forum Programming
    Replies: 1
    Last Post: 02-17-2009, 04:05 PM
  5. using COUNT for multiple tables
    By kwalt in forum Queries
    Replies: 0
    Last Post: 02-17-2009, 03:06 PM

Tags for this Thread

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