Results 1 to 2 of 2
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Exclude When Net = $0?

    I am trying to figure out to exclude all records when the net equals $0



    How to determine net $0:
    1. Invoice numbers are same
    2. UnitPrice is Positive on one and
    3. UnitPrice is Negative on the other (RETURN invoice type)


    The View with the details show these as an example:
    2016-06-18 00:00:00.000
    LTS0004832 1620287 Invoice DUES-LTSMB LTS-NMEM 12.00000
    2016-07-07 00:00:00.000 LTS0004832 1620287 Return DUES-LTSMB LTS-NMEM -12.00000
    2016-08-03 00:00:00.000 LTS0004883 1624784 Return DUES-LTSMB LTS-NMEM -12.00000
    2016-07-06 00:00:00.000 LTS0004962 2870 Invoice DUES-LTSMB LTS-PRGM 50.00000

    The ones in red cancels each other out. Net $0
    Want to exclude those records in the result.


    Have this but keep timing out (uses the View the details from the above is resulting)
    Code:
    SELECT        Invoice, PaymentDate, MbrshipNo, ItemDescr, InvType, ClassID, ItemNo, ExtPrice, CustNo, CustName, OrgMbrNo
    FROM            dbo.AE_ExceptionInvoiceReport
    WHERE        (Invoice NOT IN
                                 (SELECT        Invoice
                                   FROM            dbo.AE_ExceptionInvoiceReport AS tmp
                                   GROUP BY Invoice
                                   HAVING         (COUNT(*) > 1) AND (SUM(ExtPrice) <> 0)))

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    create a query, Q1, that has all your criteria above...all zeros.
    then create a query using outer join on Q1, to pull the opposite of that.

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

Similar Threads

  1. Exclude When - nestled?
    By aellistechsupport in forum SQL Server
    Replies: 3
    Last Post: 08-08-2016, 11:53 AM
  2. Exclude when the current month is the month to exclude?
    By aellistechsupport in forum Queries
    Replies: 15
    Last Post: 05-16-2015, 09:49 PM
  3. Exclude any that match
    By WithoutPause in forum Queries
    Replies: 1
    Last Post: 09-23-2014, 09:53 PM
  4. exclude the most recent date
    By crowegreg in forum Queries
    Replies: 2
    Last Post: 10-22-2013, 09:53 AM
  5. VBA Code to exclude codes
    By kwooten in forum Programming
    Replies: 1
    Last Post: 09-10-2013, 10:39 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