Results 1 to 7 of 7
  1. #1
    voisin1972 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Montreal
    Posts
    2

    Agregation error that I don't understand could be a quick fix from you expert


    in a simple table query, I try to Get only repeat call that are between 15min and 3 days.

    Click image for larger version. 

Name:	DupeInRawTable.PNG 
Views:	26 
Size:	3.2 KB 
ID:	31648

    Code:
    SELECT 
    DupeInRAW.SUBSCRIBER_NUMBER, 
    DupeInRAW.DEPT, 
    DupeInRAW.[Chat#DIRECTOR_NAME], 
    DupeInRAW.[Chat#OPS_MGR_NAME], 
    DupeInRAW.[Chat#TEAM_MGR_NAME], 
    DupeInRAW.[Chat#Agent_Name], 
    DupeInRAW.TECHNICAL_RESULT, 
    DupeInRAW.CHAT_DATE_MST AS date1, 
    DupeInRAW_1.CHAT_DATE_MST AS date2, 
    DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST) AS Diff
    
    
    FROM DupeInRAW, DupeInRAW AS DupeInRAW_1
    
    
    GROUP BY 
    DupeInRAW.SUBSCRIBER_NUMBER, 
    DupeInRAW.DEPT, 
    DupeInRAW.[Chat#DIRECTOR_NAME], 
    DupeInRAW.[Chat#OPS_MGR_NAME], 
    DupeInRAW.[Chat#TEAM_MGR_NAME], 
    DupeInRAW.[Chat#Agent_Name], 
    DupeInRAW.TECHNICAL_RESULT, 
    DupeInRAW.CHAT_DATE_MST, 
    DupeInRAW_1.CHAT_DATE_MST, 
    DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST),
    DupeInRAW.SUBSCRIBER_NUMBER,
    DupeInRAW.CHAT_DATE_MST
    
    
    HAVING (
    (
    DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST)<259200 
    And 
    DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST)>900
    )
    AND
    DupeInRAW.SUBSCRIBER_NUMBER=DupeInRAW_1.SUBSCRIBER_NUMBER 
    AND 
    DupeInRAW.CHAT_DATE_MST<DupeInRAW_1.CHAT_DATE_MST
    )
    ORDER BY DupeInRAW.CHAT_DATE_MST;

  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
    What is the error number and description?

  3. #3
    voisin1972 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Montreal
    Posts
    2
    Click image for larger version. 

Name:	access error.PNG 
Views:	21 
Size:	11.8 KB 
ID:	31649

    no numbers,


    Quote Originally Posted by orange View Post
    What is the error number and description?

  4. #4
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    I am not a pro by any stretch of the imagination and that is a lot of information to look at.

    I believe the attributes you have in the HAVING clause must be listed in the GROUP BY clause for aggregate function because it is in essence the same as the WHERE clause (just had similar problem). Is it possible to try something like the following, where you place all your attributes and evaluations inside the (...) and then nest it inside the SELECT statement designed for the ORDER BY since GROUP BY and ORDER BY do not function together in Access within the same query string but does function when nesting sub queries?

    SELECT * FROM (SELECT whatYouNeed FROM whereYouWant GROUP BY whatYouWant HAVING whatIsRequired) AS anAlias ORDER BY howYouWantIt DESC;

    Alternatively you could save the meat of the inner statement in a query and call on it for the potatoes to make it more compact like:

    SELECT * FROM yourQuery ORDER BY howYouWantIt DESC;

    Either way I alleviated the aggregate function error by nesting the meat of my statement inside the potatoes for ordering.

    You did not specify how you wanted to order your data with the last bit ORDER BY .... ASC/DESC; even though it will not function as stated already and it is not absolutely necessary if you desired it sorted to ascending by default but it does not hurt to specify "ASC;". You have multiple exact entries in GROUP BY for [DupeInRAW].[SUBSCRIBER_NUMBER] and [DupeInRAW].[CHAT_DATE_MST] but do not have an entry in the GROUP BY for [DupeInRAW_1].[SUBSCRIBER_NUMBER] which is located in the HAVING clause. Therefore, at the very least perhaps add [DupeInRAW_1].[SUBSCRIBER_NUMBER] into your GROUP BY and maybe remove the erroneous duplicate entries as stated already. In my opinion, the reason why you are receiving the long error message for not having an expression (the HAVING condition) of the aggregate function in the GROUP BY clause is because you have your HAVING clause nested ((()())), otherwise it would simply alert that you needed [DupeInRAW_1].[SUBSCRIBER_NUMBER].

    Again, just my two cents. I hope it helps.
    Last edited by SierraJuliet; 01-04-2018 at 01:30 PM.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You probably are using an aggregate function (MAX, FIRST, etc) on a field in the SELECT portion that is neither part of the WHERE (or HAVING) portion nor a GROUP BY clause. Or look at the query design and if my guess doesn't help, create a copy of the query and simplify as much as possible, adding aggregate functions as you go. Sometimes, you just can't do what you want with Totals queries; sometimes you have to also group by the field using the function (include it twice) but uncheck the show box for the group by field you're using the function on.

    In the first post of this answer, I said you should post the entire sql. After posting, I see it's in the code that my eyes just skipped over for some reason. I'll take a close look now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    what looks odd to me:
    - you're not using any aggregate function yet there is a HAVING clause (which filters data after an aggregate function has been applied)
    - DupeInRAW_1 fields are part of the HAVING clause but none of them are part of the SELECT clause
    - I don't get this FROM DupeInRAW, DupeInRAW AS DupeInRAW_1. There are no joins between what is either the same table where _1 is an alias, or another table.
    - it looks like this expression result
    Code:
    ((DateDiff("s",DupeInRAW.CHAT_DATE_MST,DupeInRAW_1.CHAT_DATE_MST)<259200
    isn't related to any field. If I substitute numbers for the expressions (as if they were evaluated) I read it as "HAVING (250200<259200 And 1200>900)". To me, that certainly fits the error you get since I don't know what field to apply the result to (so I suspect Access doesn't either). I might be right out to lunch, but that's my take.
    Last edited by Micron; 01-04-2018 at 12:16 PM. Reason: correction

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    FROM DupeInRAW, DupeInRAW AS DupeInRAW_1
    This is a Cartesian Join: if "DupeInRAW" had 10 records, then a query based on the above FROM clause would return 100 records???? Is this what is wanted??

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

Similar Threads

  1. code error I don't understand why
    By LaughingBull in forum Access
    Replies: 5
    Last Post: 06-21-2017, 06:01 PM
  2. Agregation function - where clause
    By Khalil Handal in forum Access
    Replies: 3
    Last Post: 05-18-2017, 07:22 AM
  3. Replies: 5
    Last Post: 07-10-2013, 05:07 AM
  4. Replies: 2
    Last Post: 07-11-2011, 05:34 AM
  5. Need Expert Help!! Thank you!
    By Antflash in forum Access
    Replies: 3
    Last Post: 08-18-2010, 05:09 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