Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry, it's developing and started with one way with the goal, but it appears to focus ultimately on the goal and explaining the goal better is the way to go ...



    so ...
    Looking for:
    EXCLUDE Invoices WHEN it ONLY has ADMINFEE and /or SHIPPING AND WITHOUT Registration

    I thought saying ONLY has ADMINFEE and/or SHIPPING is sufficient, but to be sure, as added assurance, AND WITHOUT Registration, because to say it ONLY has, it would be it can't have any other line on the invoice.

    As shown in the sample results set. Exclude where it has ONLY ADMINFEE and/or SHIPPING (has both these BUT does NOT have Registration). It's marked in RED (invno = LTS0001446) WHEREAS the other two invoices have ADMINFEE and/or SHIPPING BUT has a Registration line on the invoices.

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry, I must be dense.

    In addition to you taking 11 posts to provide the full sql and previously providing sql which was not your actual sql causing the problem

    I've explained why your subquery doesn't work - no response
    I've provided the correction - no response
    I've provided a more efficient (i.e. faster performance) construction of your query - no response
    I've asked for clarification of your latest variation - and regret the explanation does not make sense

    WHEN it ONLY has ADMINFEE and /or SHIPPING AND WITHOUT Registration

    If it only has adminfee or shipping records, it can't have a registration - so what am I missing

    I'm not going to spend my time trying to understand this latest variation until you have to courtesy to respond to the points above and explained why they don't work and why they are not a solution and for the last point explain what I am missing

  3. #18
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry, I did not see a correction? I may have missed it in the way the link to the reply post comes up when the link is clicked?
    if you're referring to reply post #12, that does not work.
    i have tried both the IN and the <> on ClassID

    My reply post #14 explains and illustrates the result sought.

    I think it's best for forget all else but the rephrased goal.

    Which again is to
    EXCLUDE WHEN an Invoice ONLY has ADMINFEE and /or SHIPPING AND WITHOUT Registration

    So what is the correct syntax to handle the above statement to exclude when?
    I am not seeing the code as the solution for this??


    Thank you

  4. #19
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you're referring to reply post #12
    no - post #15, the one you just replied to in your previous post.

  5. #20
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    I'm sorry, there is no solution to the goal that I can make sense of or code / proper syntax that actually Excludes records When an Invoice has Shipping/Admin fee AND without a Registration line)

    Line item 1 = Registration and it's amount
    Line item 2 = ADMINFEE and it's amount - not all invoices will have a Adminfee line
    Line item 3 = SHIPPING and it's amount - not all invoices will have a shipping line

    Again, what is the actual correct syntax to look at all the invoices and Exclude those that do not have a Registration line but has an ADMINFEE and/or SHIPPING lines?

  6. #21
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there is no solution to the goal that I can make sense of
    I usually enjoy a challenge, but this has beaten me. I am unable to help if you do not answer my questions.

    Good luck with your project but I will not be responding to further questions on this thread

  7. #22
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by Ajax View Post
    Sorry, I must be dense.

    In addition to you taking 11 posts to provide the full sql and previously providing sql which was not your actual sql causing the problem

    I've explained why your subquery doesn't work - no response <-- no reply necessary as it does not provide the solution to exclude invoices with no registration, etc ...
    I've provided the correction - no response <-- I responded and stated I don't know what correction is as whatever it is I am not seeing a solution to exclude invoices with no registration, etc ...
    I've provided a more efficient (i.e. faster performance) construction of your query - no response <-- again, nothing to will exclude invoices with no registration, etc ...
    I've asked for clarification of your latest variation - and regret the explanation does not make sense <-- sorry it does not make sense. I thought it was quite simple. To exclude all invoices when there is no registration line but has an ADMINFEE and/or SHIPPING line.
    As illustrated, an invoice may have 1 to many lines. Of which in this case there are possibility of up to 3 lines:
    Once again:
    1. Registration and it's amount line item - sometimes there may not be a registration line item
    2. ADMINFEE and it's amount line item - sometimes there may not be a shipping line item
    3. SHIPPING and it's amount line item - sometimes there may not be a shipping line item
    There can be any combination of the above.

    Looking for code to exclude invoices where it does not have a registration line item but has an ADMINFEE and/or SHIPPING line items.


    WHEN it ONLY has ADMINFEE and /or SHIPPING AND WITHOUT Registration

    If it only has adminfee or shipping records, it can't have a registration - so what am I missing <-- correct, so this type of invoice without a Registration should be excluded.

    I'm not going to spend my time trying to understand this latest variation until you have to courtesy to respond to the points above and explained why they don't work and why they are not a solution and for the last point explain what I am missing
    I apology for not responding to the 4 points, they didn't seem meaningful as they did not get this thread to the goal. What should is the goal itself, which again is:

    EXCLUDE all invoices that does not have a registration line item but has an ADMINFEE and/or SHIPPING line item.

    What else is it that one needs to know?
    1. What's in an invoice - answered
    2. What should be excluded - answered

    my reply #16
    ultimately the goal is to not display any invoice ONLY having SHIPPING and/or ADMINFEE

    So in this sample, InvNo LTS0001446 in red would be excluded in the result

    LTS0001445 1619953 12.00000 0.00000 ORG00002838 DUES-LTSMB
    LTS0001445 1916 0.00000 0.25000 ORG00002838 SHIPPING
    LTS0001445 1916 0.00000 1.00000 ORG00002838 ADMINFEE
    LTS0001446
    2753 0.00000 0.25000 ORG00004402 SHIPPING
    LTS0001446 2753 0.00000 1.00000 ORG00004402 ADMINFEE
    LTS0001451 1619966 12.00000 0.00000 PER01592434 DUES-LTSMB
    LTS0001451 1619966 0.00000 0.25000 PER01592434 SHIPPING
    LTS0001451 1619966 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'm trying to understand the relevance of anything else. I went again back through your replies and, honestly, I do not have anything that helps with the goal. Not one reply post has any solution to the goal

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select Count Problem
    By ndehhh in forum Programming
    Replies: 2
    Last Post: 10-19-2015, 02:56 PM
  2. Adding a Count function to current select statement
    By johnson8809 in forum Queries
    Replies: 2
    Last Post: 02-21-2015, 07:32 PM
  3. Replies: 7
    Last Post: 12-15-2013, 08:42 PM
  4. Select, Count, Where (Like) HELP!
    By nols76 in forum Queries
    Replies: 3
    Last Post: 08-24-2011, 11:36 AM
  5. count and switch function in same select query
    By sandlucky in forum Queries
    Replies: 2
    Last Post: 04-08-2011, 11:16 PM

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