Error 3122 when Select Field is not in Group By 1

[tbonehwd]

If I use the code in the query below it works, its just not exactly what I want. I only want to Group by PROOLN_M.ITM_NUM if I try and remove the other fields in the Group By I get "Error 3122 You tried to execute a query that does not include the specified expression 'DCS_001' as part of an aggregate function.

Can someone give me some insight of what I am doing wrong?

strSQL = "SELECT PROOLN_M.ITM_NUM, PROOLN_M.DSC_001, PROOLN_M.DSC_003, CDSITM_M.CPR_YER " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_PUR " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_RET  " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_PUR  " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_RET  " & _
         "FROM ((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) INNER JOIN CDSCTM_M ON PROORD_M.CTM_NBR = CDSCTM_M.CTM_NBR) INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM=CDSITM_M.ITM_NUM " & _
         "WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') " & _
         "AND CDSCTM_M.CTM_TYP = 'BKST' & _
         "AND PROOLN_M.ITM_NUM = '0006' & _
         "AND CDSITM_M.ITM_SAC = 'BCS' & _
         "AND PROORD_M.ACT_DTE >=#7/31/05# & _
         "AND PROORD_M.ACT_DTE <=#8/31/05# & _
         "GROUP BY PROOLN_M.ITM_NUM, PROOLN_M.DSC_001, PROOLN_M.DSC_003, CDSITM_M.CPR_YER ;"

 

[PHV]

If you don't care which DSC_001, DSC_003 and CPR_YER values are retrieved for each ITM_NUM then use an arbitrary aggregate function (Min, Max, First or Last):
strSQL = "SELECT PROOLN_M.ITM_NUM, Min(PROOLN_M.DSC_001), Min(PROOLN_M.DSC_003), Min(CDSITM_M.CPR_YER) " & _
...
"GROUP BY PROOLN_M.ITM_NUM"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

Thank you thats exactly what I needed it works like a dream!