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

    In Select Having Count >1?

    What is the equivalent of
    Code:
    In (SELECT [InvNo] FROM [dbo_AE_DeferredSalesMemberships] As Tmp GROUP BY [InvNo] HAVING Count(*)>1)
    In SQL?

    I tried a couple different ways but can't seem to get it correct.

    Goal:


    To only display records where there are more than one detail line of the same invoice number
    Last edited by aellistechsupport; 07-31-2016 at 01:15 PM. Reason: Incorrect code

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest use the find duplicates wizard to get the correct syntax - without checking, looks like =1 should be >1

    otherwise provide the full sql to the query, you are only showing half the story and without knowing the full story we can only guess which would be the right solution.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by Ajax View Post
    suggest use the find duplicates wizard to get the correct syntax - without checking, looks like =1 should be >1

    otherwise provide the full sql to the query, you are only showing half the story and without knowing the full story we can only guess which would be the right solution.
    Sorry for the confusion, it works fine in MS Access.
    My question was, how do I write in so it works in SQL? On SQL Server?
    What is the equivalent syntax for SQL on SQL Server?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm confused as to why your goal is
    Goal:
    To only display records where there are more than one detail line of the same invoice number
    but the subquery returns records where there is only one 1 record

    so again, without knowing the full sql, could be a number of solutions. At the moment, I'm assuming what you have provided is a criteria which in this case does not provide the right data (although you say it does) and it is inefficient - I would have expected your subquery to be a view used as a table, and not as a criteria (which I'm assuming it is)

    select *
    from dbo_AE_DeferredSalesMemberships O inner join (SELECT [InvNo] FROM [dbo_AE_DeferredSalesMemberships] Tmp GROUP BY [InvNo] HAVING Count(*)=1) T ON O.Invno=T.invno

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Sorry for the confusion
    As stated in the title as well as specified in goal it's >1

    Invoices generally have more than one line item

    As noted, the goal is to only list invoices with more than one line item

    The code provided with the edit with >1 works great in Access
    I am trying to find the code that works in SQL on SQL server
    I applied the Access SQL but it does not work on SQL server

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For clarity:

    You have an Access front end (access 2013/32 bit) and a SQL server Back end???

  7. #7
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by orange View Post
    For clarity:

    You have an Access front end (access 2013/32 bit) and a SQL server Back end???
    No not necessarily.

    I only and simply stated that the code i have in Access works and in Access only and i'm looking for a code that will work on the SQL Server.
    IF the need can be satisfied in doing in Access, I would, as stated it works in an Access query but need a code that works on the SQL Server.

    As noted, the GOAL is to create a query or view on the SQL Server to produce results for only those invoice details where there is more than one line / detail.
    Count >1 on InvoiceNumber

    Another reason why this is posted under the SQL Server forum

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    why are you unable to provide the full sql code? - at the moment your question is akin to ringing your mechanic and saying 'why won't my car start, I've got a steering column where I put the key'

    All you have done is provide a small part of the code which in itself appears to have the correct syntax for the subquery and said it doesn't work = why not? wrong result?, syntax error? if the latter what is the error message?. An alternative solution instead of using IN has been provided and you have not commented on as to whether it works or not (and why not).

    Without more information from you I fear your question will stall.

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by Ajax View Post
    why are you unable to provide the full sql code? - at the moment your question is akin to ringing your mechanic and saying 'why won't my car start, I've got a steering column where I put the key'

    All you have done is provide a small part of the code which in itself appears to have the correct syntax for the subquery and said it doesn't work = why not? wrong result?, syntax error? if the latter what is the error message?. An alternative solution instead of using IN has been provided and you have not commented on as to whether it works or not (and why not).

    Without more information from you I fear your question will stall.
    I am unsure how many different ways I can state that the criteria / parameter code that works in Access does not work in SQL Server Query or View?

    The full SQL Code in Access wouldn't work, again since it's not the same table or fields, etc ... the ONLY thing I am looking for is the correct syntax / code to work on the SQL Server that mimics the syntax / code that works in the Access query.

    Which is what I shared. Since THAT is the only line that is relevant.
    I copied the code and tried to use in the SQL Server View and it simply errors. It's not the correct syntax for SQL Server.

    Hence, I am here trying to see if someone knows. Thanks

  10. #10
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    So i finally got the syntax to produces results ... however ...

    Here's a result of sample data set:

    InvNo MbrNo RegistrationFee Shipping/ProcessingFee ItemID CustNo ClassID
    LTS0001445 1619953 $ 12.00 $ - LTS-NMEM ORG00002838 DUES-LTSMB
    LTS0001445 1916 $ - $ 0.25 FRGHT-LTSMB ORG00002838 SHIPPING
    LTS0001445 1916 $ - $ 1.00 ADMFEE ORG00002838 ADMINFEE
    LTS0001446 2753 $ - $ 0.25 FRGHT-LTSMB ORG00004402 SHIPPING
    LTS0001446 2753 $ - $ 1.00 ADMFEE ORG00004402 ADMINFEE
    LTS0001451 1619966 $ 12.00 $ - LTS-NMEM PER01592434 DUES-LTSMB
    LTS0001451 1619966 $ - $ 0.25 FRGHT-LTSMB PER01592434 SHIPPING
    LTS0001451 1619966 $ - $ 1.00 ADMFEE PER01592434 ADMINFEE


    I think I need more than just HAVING Count(*) >1 on InvNo.

    The InvNo LTS0001446, having ONLY SHIPPING and ADMINFEE should also be suppressed from displaying.

    Code:
    (SalesHistoryPaymentMethodAndPayments.InvNo IN
                                 (SELECT        InvNo
                                   FROM            SalesHistoryPaymentMethodAndPayments AS Temp
                                   WHERE        (ClassID = 'ADMINFEE') OR
                                                             (ClassID = 'SHIPPING')
                                   GROUP BY InvNo
                                   HAVING         (COUNT(*) > 1)))
    is not producing the required results

    I think I'll start a new one since the original inquiry was to get the syntax to produce results

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am unsure how many different ways I can state that the criteria / parameter code that works in Access does not work in SQL Server Query or View?
    What is the big picture -- in very broad terms, plain simple English?

    If you're running a vb.net or C# front end against a SQL server backend, then that's important.
    If you are using some other code and not an Access front end, then that's important.
    If it's an Access frontend against a SQL server backend, then Access SQL syntax is needed.
    If you're running Access front end, and a passthrough query then you need SQL server SQL syntax.

    You may think you have provided all the info required, but we're at post #11 and still trying to get the requirements straight.
    I suggest you show the original data, the intended algorithm in plain English and the expected result. That might reduce the confusion/ambiguity of your post.


    Good luck with your project.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    would have thought it should be

    WHERE (ClassID <> 'ADMINFEE') OR
    (ClassID <> 'SHIPPING')

    or

    WHERE ClassID NOT IN ('ADMINFEE','SHIPPING')

    but still say this is not an efficient query - should be more like the query I posted in #4

  13. #13
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ajax,
    I tried that.

    So here's the full SQL WITHIN Access and it SOMEWHAT works IN Access, where the InvNo = LTS0001446, from the sample results shown is NOT showing.

    Code:
    SELECT dbo_v010LearnToSkateMembersPaymentsDetails.[InvNo], dbo_v010LearnToSkateMembersPaymentsDetails.[USFSANo], dbo_v010LearnToSkateMembersPaymentsDetails.[Registration Fee], dbo_v010LearnToSkateMembersPaymentsDetails.[Shipping/Processing], dbo_v010LearnToSkateMembersPaymentsDetails.[ItemNo], dbo_v010LearnToSkateMembersPaymentsDetails.[CustNo], dbo_v010LearnToSkateMembersPaymentsDetails.[ClassID]
    FROM dbo_v010LearnToSkateMembersPaymentsDetails
    WHERE (((dbo_v010LearnToSkateMembersPaymentsDetails.[InvNo]) In (SELECT [InvNo] FROM [dbo_v010LearnToSkateMembersPaymentsDetails] As Tmp WHERE ClassID <> 'ADMINFEE' GROUP BY [InvNo] HAVING Count(*)>1 )))
    ORDER BY dbo_v010LearnToSkateMembersPaymentsDetails.[InvNo];
    InvNo USFSANo Registration Fee Shipping/Processing ItemNo CustNo ClassID
    LTS0001445 1619953 12 0 LTS-NMEM ORG00002838 DUES-LTSMB
    LTS0001445 1916 0 0.25 FRGHT-LTSMB ORG00002838 SHIPPING
    LTS0001445 1916 0 0.25 FRGHT-LTSMB ORG00002838 SHIPPING
    LTS0001451 1619966 12 0 LTS-NMEM PER01592434 DUES-LTSMB
    LTS0001451 1619966 12 0 LTS-NMEM PER01592434 DUES-LTSMB
    LTS0001451 1619966 12 0 LTS-NMEM PER01592434 DUES-LTSMB


    HOWEVER, it's incorrect result AND in SQL View, it is STILL producing the InvNo = LTS0001446
    I do NOT fully understand the difference in Access vs on SQL Server, it's the SAME syntax and yet it produces the correct result in Access and NOT on SQL Server

    On SQL Server
    Code:
    SELECT        InvNo, USFSANo, [Registration Fee], [Shipping/Processing], ItemNo, CustNo, ClassID
    FROM            dbo.v010LearnToSkateMembersPaymentsDetails
    WHERE        (InvNo IN
                                 (SELECT        dbo.v010LearnToSkateMembersPaymentsDetails.InvNo
                                   FROM            dbo.v010LearnToSkateMembersPaymentsDetails AS Tmp
                                   WHERE        (ClassID <> 'ADMINFEE')
                                   GROUP BY InvNo
                                   HAVING         (COUNT(*) > 1)))
    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


    Orange, does this help?

    GOAL:
    Trying to EXCLUDE all invoices where it ONLY has ADMINFEE and /or SHIPPING ... WITHOUT a Registration Fee on the Invoice. So in the sample result above with the LTS0001446 in RED, these types of invoices should be excluded.

  14. #14
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    How about this?
    What is the correct syntax to say: exclude all invoices where the invoice ONLY has SHIPPING and/or ADMINFEE?

    trying to exclude those invoices altogether.


    Perhaps code to say something like, IF the invoice does not have a Registration Fee then exclude the entire invoice?

    since 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

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    every time you post something, it changes and you have still not commented on the example I provided.

    However at least we now have the full sql - the reason it fails in sql server is because despite what you say, it is not the same as in access - in access (and what you have been saying up to now) you have

    In (SELECT [InvNo] FROM

    in sql server you have

    IN (SELECT dbo.v010LearnToSkateMembersPaymentsDetails.InvNo

    I'm sure you can see the difference - in SQL server you are referencing the invno in the main query, not the subquery which has the table aliased. So make it the same as access.

    So far as I can see, your access sql is also producing the wrong result - invoice LTS0001445 should be producing a ADMINFEE record (per sql server) but is producing 2 shipping fee records instead. Something similar for LTS0001451 as well. but correctly not showing the LTS0001446 invoice

    the Sql server query returns 7 columns, but you are only displaying 6 (ItemNo is not showing) - which hides your issue

    you are saying

    Trying to EXCLUDE all invoices where it ONLY has ADMINFEE and /or SHIPPING ... WITHOUT a Registration Fee on the Invoice
    is this ONLY has ADMINFEE and /or SHIPPING AND WITHOUT... or ONLY has ADMINFEE and /or SHIPPING OR WITHOUT... or
    ONLY has ADMINFEE and /or SHIPPING AND/OR WITHOUT...

    either way, there is no mention of filtering on the registration fee in your query, nor is it clear whether the registration fee relates to the adminfee or shipping record or can appear on any record.

Page 1 of 2 12 LastLast
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