Results 1 to 5 of 5
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    353

    Multiple HAVING in query

    I have the query



    SELECT tbl_ImportedRepairs.InvoiceNumber
    FROM tbl_ImportedRepairs
    WHERE tbl_ImportedRepairs.TrinityBatch IS NULL
    GROUP BY tbl_ImportedRepairs.InvoiceNumber
    HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));

    This query finds line items in a table and gives me the invoice number ONLY if all the line items have been approved.

    I would like to build on that and say:
    Give me Invoice Number if ALL line items have been approved
    AND
    All The Updated Responsibility code are 1

    I am thinking it might be like this

    SELECT tbl_ImportedRepairs.InvoiceNumber
    FROM tbl_ImportedRepairs
    WHERE tbl_ImportedRepairs.TrinityBatch IS NULL
    GROUP BY tbl_ImportedRepairs.InvoiceNumber
    HAVING (((Sum(IIf([UpdatedResponsibilityCode]=1,1,0)))=0)) AND HAVING (((Sum(IIf([BlakeApproval]=False,1,0)))=0));

    But of course that doesnt work and I am getting
    Syntax error (missing operator) in query expression '(((Sum(IIf(BlakeApproval=Flase,1,0)))=0)) AND'

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    Try only 1 HAVING.

    HAVING (((Sum(IIf([UpdatedResponsibilityCode]=1,1,0)))=0)) AND (((Sum(IIf([BlakeApproval]=False,1,0)))=0));
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,923
    The error msg posted has a "AND" at the end but I can't see that in the posted SELECT statement. If it is actually in the SELECT statement, remove it. Also not sure that the second "HAVING" is required.
    EDIT
    Sorry, it would appear that I took to long to write this post. Got a bit distracted by the football on TV.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    353
    Yes, I am having as hard a time with this query as Germany is with Algeria. I will try it without the two Having and see.

    Edit, I got distracted too

    It runs if I take out the second Having but does not return the desired result. I ran it with an invoice where they were all approved and were all UpdatedResponsibilityCode of 1 and it didnt show up.

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    353
    Spoke too early. Had to change the UpdatedResponsibilityCode = 1 to UpdatedResponsibilityCode <> 1.

    Looks good but will have to do further testing tomorrow.
    Thanks

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

Similar Threads

  1. Replies: 1
    Last Post: 04-12-2013, 03:03 PM
  2. Replies: 2
    Last Post: 04-10-2013, 03:54 PM
  3. Replies: 7
    Last Post: 01-02-2013, 11:23 PM
  4. Export multiple query's to multiple sheet excel
    By vaikz in forum Import/Export Data
    Replies: 4
    Last Post: 08-15-2012, 08:53 AM
  5. Replies: 1
    Last Post: 02-04-2012, 02:07 AM

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 - Senior Forums