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

    Exclude When - nestled?

    I am trying to figure out a syntax / code to:
    EXCLUDE all invoices that does not have a registration line item but has an ADMINFEE and/or SHIPPING line item.
    Or simply EXCLUDE all invoices that does not have a registration line item.

    So the goal is to not display any invoice ONLY having SHIPPING and/or ADMINFEE.

    Invoices may include, but not limited to, and in any combination of:
    1. Registration and it's amount - (in the sample below it only shows DUES-LTSMB, this can be other Item codes / registration type)
    2. ADMINFEE and it's amount - this is a static Item code


    3. SHIPPING and it's amount - this is a static Item code

    in this sample, InvNo LTS0001446, in red, would be excluded in the query result (using SQL View or SQL Query on SQL Server Management Studio)

    InvNo Registration Admin/Ship CustNo ItemNo
    LTS0001445 12.00000 0.00000 ORG00002838 DUES-LTSMB
    LTS0001445 0.00000 0.25000 ORG00002838 SHIPPING
    LTS0001445 0.00000 1.00000 ORG00002838 ADMINFEE
    LTS0001446
    0.00000 0.25000 ORG00004402 SHIPPING
    LTS0001446 0.00000 1.00000 ORG00004402 ADMINFEE
    LTS0001451 12.00000 0.00000 PER01592434 DUES-LTSMB
    LTS0001451 0.00000 0.25000 PER01592434 SHIPPING
    LTS0001451 0.00000 1.00000 PER01592434 ADMINFEE


    InvNo: LTS0001446 does not have a Registration line item, only SHIPPING and ADMINFEE (noting again that not all invoices have both)

    I cannot figure out how to do this.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Probably more efficient way with vba but here is one idea:

    Create 2 Queries (Uses MainTable).

    Query1 on MainTable: InvNo, ItemNo
    HIt Totals button to GroupBy fields
    For InvNo, select GroupBy, For ItemNo, select Where, in criteria for ItemNo put "ADMINFEE" or "SHIPPING"
    This will give you a distinct list of all the InvNo that should not be included

    Query2 = Link the MainTable to Query1 by InvNo, do left Join so arrow goes to right where you are selecting all records from MainTable and only those from Query1 that match
    include all fields from MainTable and InvNo field from Query1. In the criteria for Query1.InvNo enter "Is Null"

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Bulzie,
    there are two queries and it's insufficient.
    Meaning it isn't quite working.

    Then tried to do this instead.
    Code:
    NOT IN (SELECT DISTINCT dbo.v010LearnToSkateMembersPaymentsDetails.InvNo FROM dbo.v010LearnToSkateMembersPaymentsDetails AS Tmp WHERE ([Registration Fee] = 0) GROUP BY InvNo HAVING (COUNT(*) = 1))

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    What is not working, which query?

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

Similar Threads

  1. 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
  2. Exclude any that match
    By WithoutPause in forum Queries
    Replies: 1
    Last Post: 09-23-2014, 09:53 PM
  3. Exclude TOP N records from query
    By gemadan96 in forum Queries
    Replies: 4
    Last Post: 06-15-2014, 10:11 AM
  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