Results 1 to 4 of 4
  1. #1
    rhet256 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2018
    Posts
    2

    Aggregate in expression error

    I am currently trying to correct a database that is not functioning properly. I will post a snip of the error message and the code below.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	6 
Size:	6.0 KB 
ID:	34698

    [SELECT tblCrushingData.Date, Sum(IIf([CrushingProd]>0,([CrushingProd]/2000)/Sum([Uptime]),0)) AS TPH, 1 AS Expr1, 0.55 AS Expr2, tblCrushingData.Operator, tblCrushingData.Shift, tblCrushingData.[303 Start], tblCrushingData.[303 Ending], tblCrushingData.[Planned RnTm], tblCrushingData.[Planned DT], tblCrushingData.[Unplanned DT], tblCrushingData.[Uptime], tblCrushingData.[Comments1], tblCrushingData.[OEE Comments]
    FROM tblCrushingData INNER JOIN qryDayWT ON tblCrushingData.Date = qryDayWT.PrDate
    GROUP BY tblCrushingData.Date, 1, 0.55, tblCrushingData.Operator, tblCrushingData.Shift, tblCrushingData.[303 Start], tblCrushingData.[303 Ending], tblCrushingData.[Planned RnTm], tblCrushingData.[Planned DT], tblCrushingData.[Unplanned DT], tblCrushingData.Uptime, tblCrushingData.Comments1, tblCrushingData.[OEE Comments], tblCrushingData.[OEE Comments]


    HAVING (((tblCrushingData.Date)>Now()-45))
    ORDER BY tblCrushingData.Date DESC;]

  2. #2
    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
    We need more info if we are to offer advice.

    What does functioning properly mean-- perhaps you could give a couple of examples.

    You are showing us HOW you have used SQL to do something. But what is that something in simple, plain English?

  3. #3
    rhet256 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2018
    Posts
    2
    Sum(IIf([CrushingProd]>0,([CrushingProd]/2000)/Sum([Uptime]),0)) AS TPH
    This is the line that is the issues. I was handed this and told to fix it so I'm unsure of some of the meaning, but this line should convert [CrushingProd] pulled from a table to tons by dividing by 2000. It then divides by the Sum([Uptime]) to yield tons per hour

  4. #4
    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
    What does "fix" mean to you? Was this ever working? Do you have some sort of test procedures?

    The meaning of 0.55 AS Expr2 is confusing?

    It is often easier to take a complex situation/query and break it into smaller pieces - resolve/understand the smaller piece - then add another part of the puzzle ---resolve/understand and repeat. Get a select query working before going to aggregation.

    Giving readers SQL (that you don't understand) with a query ( name only) as part of a join is not likely to get you focused advice.

    Suggest you give some description of application in plain English, show your table/relationship design; post the full SQL of the query qryDayWT.

    Good luck.

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

Similar Threads

  1. Using a non-aggregate expression in another expression
    By threegoldstars in forum Access
    Replies: 8
    Last Post: 07-08-2017, 02:02 AM
  2. Replies: 1
    Last Post: 06-14-2015, 09:48 AM
  3. Replies: 5
    Last Post: 11-19-2012, 01:44 PM
  4. aggregate error due to function
    By boutwater in forum Access
    Replies: 2
    Last Post: 09-26-2011, 03:39 PM
  5. an aggregate function error message
    By newtoAccess in forum Queries
    Replies: 1
    Last Post: 11-27-2010, 05:18 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