Results 1 to 12 of 12
  1. #1
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49

    SQL Server Backend - Each GROUP BY expression must contain at least one column that is not an outer

    Hello, I am just learning to use SQL server as my backend. I just started testing and got the error "each group by expression must contain at least one column that is not an outer"

    Here is the SQL:

    INSERT INTO [Part Shortages] ( [Raw Material], Quantity, Status, Description, [Fill Room Location], Reason, [Expected Arrival Date] )
    SELECT [Add Part Shortages].[Raw Material Number], [Add Part Shortages].[Shortage Qty], [Add Part Shortages].Expr1, [Add Part Shortages].[Decoration Description], dbo_Items.RECEIVING_LOCATOR, [Add Part Shortages].[Material Reason], [Add Part Shortages].[Material Due Date]


    FROM dbo_Items RIGHT JOIN ([Add Part Shortages] LEFT JOIN [Add Part Shortages Prequel] ON ([Add Part Shortages].[Raw Material Number] = [Add Part Shortages Prequel].[Raw Material]) AND ([Add Part Shortages].Expr1 = [Add Part Shortages Prequel].Status)) ON dbo_Items.ITEM_NUMBER = [Add Part Shortages].[Raw Material Number]
    WHERE ((([Add Part Shortages Prequel].Status) Is Null));

    What is SQL server not liking here?

    Thanks for your help!
    Chad

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What is [Add Part Shortages].Expr1 ?

    In fact what is the whole query for Add Part Shortages ?

    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    I think I have violated the "group by literal" rule here when I use the constant "Open". But I don't know how to change it to make it work.

    "ADD PART SHORTAGES"


    SELECT [Line Item Detail].[Raw Material Number], Sum([Line Item Detail].[Shortage Qty]) AS [Shortage Qty], "Open" AS Expr1, [Line Item Detail].[Decoration Description], [Line Item Detail].[Material Due Date], [Line Item Detail].[Material Reason]
    FROM [Issue Header] INNER JOIN ([Sales Order Header] INNER JOIN [Line Item Detail] ON [Sales Order Header].ID = [Line Item Detail].[Connection ID]) ON [Issue Header].ID = [Sales Order Header].Connector
    GROUP BY [Line Item Detail].[Raw Material Number], "Open", [Line Item Detail].[Decoration Description], [Line Item Detail].[Material Due Date], [Line Item Detail].[Material Reason], [Issue Header].[Order Level Status]
    HAVING ((([Line Item Detail].[Raw Material Number]) Is Not Null) AND ((Sum([Line Item Detail].[Shortage Qty]))>0) AND (([Issue Header].[Order Level Status])="Open"));

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you haven't specified the join detail

    FROM dbo_Items RIGHT JOIN ([Add Part Shortages] LEFT JOIN [Add Part Shortages Prequel]

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    and the same with your new query

    FROM [Issue Header] INNER JOIN ([Sales Order Header] INNER JOIN [Line Item Detail]

  6. #6
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Quote Originally Posted by Ajax View Post
    you haven't specified the join detail

    FROM dbo_Items RIGHT JOIN ([Add Part Shortages] LEFT JOIN [Add Part Shortages Prequel]

    The second post I made is "Add Part Shortages"

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That Query doesn't make much sense I'm afraid.

    You are specifying Expr1 as 'Open' , so assuming the rest of the criteria pull the correct details remove that from the Having clause.
    You then refer to
    Code:
    AND (([Issue Header].[Order Level Status])="Open"));
    But isn't that the field you have just "created" as Expr 1?
    so assuming the rest of the criteria pull the correct details remove that from the Having clause as a start and see if that helps.

    Also in SQL server strings are always surrounded by single quotes ' not " , so if you are doing this in SQL server management studio it would error out immediately.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The second post I made is "Add Part Shortages"
    you are missing the point, when joining multiple tables you use


    FROM (T1 inner join T2 on T1.PK=T2.FK) inner join T3 ON …….

  9. #9
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Quote Originally Posted by Ajax View Post
    you are missing the point, when joining multiple tables you use


    FROM (T1 inner join T2 on T1.PK=T2.FK) inner join T3 ON …….
    I am probably missing the point because I put this together in Access' graphical interface. What I pasted is simply the resulting SQL that was generated. I am not that good at writing the SQL.

  10. #10
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Quote Originally Posted by Minty View Post
    That Query doesn't make much sense I'm afraid.

    You are specifying Expr1 as 'Open' , so assuming the rest of the criteria pull the correct details remove that from the Having clause.
    You then refer to
    Code:
    AND (([Issue Header].[Order Level Status])="Open"));
    But isn't that the field you have just "created" as Expr 1?
    so assuming the rest of the criteria pull the correct details remove that from the Having clause as a start and see if that helps.

    Also in SQL server strings are always surrounded by single quotes ' not " , so if you are doing this in SQL server management studio it would error out immediately.

    Where do I put the following in my SQL statement?

    Code:
    AND (([Issue Header].[Order Level Status])="Open"));
    Click image for larger version. 

Name:	Forum.jpg 
Views:	8 
Size:	112.4 KB 
ID:	35134

  11. #11
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think we need to see some sample data and your expected output.
    You can post a sample table here with the expected results or upload a small stripped down zipped up database, with enough data to get to your desired results.

    Make sure you cover all possibilities with your sample data.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    I changed Expr1 to an expression instead of group by. I think that may have been the problem.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2018, 04:30 PM
  2. Replies: 4
    Last Post: 09-05-2013, 08:02 AM
  3. .MDB Backend on a 64bit 2012 server
    By chrispl in forum Access
    Replies: 1
    Last Post: 08-09-2013, 09:29 AM
  4. Replies: 7
    Last Post: 04-05-2012, 07:38 AM
  5. Replies: 2
    Last Post: 03-24-2012, 01:29 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