July 9th, 2003, 07:03 AM
|
|
Junior Member
|
|
Join Date: Jun 2003
Posts: 28
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
GROUP BY with less fields than SELECT requires?
Code:
INSERT INTO EXCEL_TEMP_PUR ( PROJID, WORK_NUMBER, LAJI, ORDERID, ORDER_ROWNUM, ITEMID, ITEMNAME, COMPANYNAME, SIDOTUT )
SELECT PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, Left(DLG_ITEM.ITEM_TYPE_NAME,2) AS LAJI, PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ORDER_ROWNUM, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME, Sum(PUR_ORDERBATCH.AMOUNT_BATCH) AS SumOfAMOUNT_BATCH
FROM PUR_ORDERBATCH, DLG_ITEM
WHERE (((PUR_ORDERBATCH.PROJID) Is Not Null) AND ((DLG_ITEM.ITEMID)=[PUR_ORDERBATCH].[ITEMID]))
GROUP BY PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, Left(DLG_ITEM.ITEM_TYPE_NAME,2), PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ORDER_ROWNUM, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME;
This query returns the fields I want (need) as stated in the select query, and they are grouped by the same values in the group by part of the query.
However, I do not want to group by with all those fields, I would like to group without these 3 fields, ITEMID, ITEMNAME, COMPNAME, however, if I try to just remove those fields, access will say: "You tried to execute a query that doesn't include the specified expresssion 'ITEMID' as part of an aggregate function."
Has anyone had the same problem? Know of a workaround?
Hena
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
