Shipment Summary

[dtmb]

This is a query I created from invdat

SELECT ICSHIPD.FMTITEMNO, ICSHIPD.ITEMDESC, ICSHIPD.SHIPQTY, ICSHIPD.STOCKUNIT, ICSHIPD.UNITCOST, ICSHIPD.SHIPCOST, ICSHIPD.ICACCT, ICSHIPH.FISCYEAR, ICSHIPH.FISCPERIOD, ICSHIPH.CUSTNO, ICSHIPH.CUSTNAME, ICSHIPD.EXPRICEHM FROM ICSHIPD ICSHIPD, ICSHIPH ICSHIPH WHERE ICSHIPD.DAYENDSEQ = ICSHIPH.DAYENDSEQ AND ICSHIPD.TRANSSEQ = ICSHIPH.TRANSSEQ AND ((ICSHIPH.FISCYEAR='2007') AND (ICSHIPH.FISCPERIOD=5)) ORDER BY ICSHIPH.CUSTNO, ICSHIPD.ICACCT, ICSHIPD.ITEMDESC

Each customer orders each product about 20 times per period, how can I get the query to select the itemdesc only once for each customer and summarize the shipqty and the shipcost for that particular itemdesc

Thanks in advance for your assistance.

 

[ettienne]

SELECT ICSHIPD.FMTITEMNO, ICSHIPD.ITEMDESC, Sum(ICSHIPD.SHIPQTY) AS SumOfSHIPQTY, ICSHIPD.STOCKUNIT, Sum(ICSHIPD.UNITCOST) AS SumOfUNITCOST, Sum(ICSHIPD.SHIPCOST) AS SumOfSHIPCOST, ICSHIPD.ICACCT, ICSHIPH.FISCYEAR, ICSHIPH.FISCPERIOD, ICSHIPH.CUSTNO, ICSHIPH.CUSTNAME, ICSHIPD.EXPRICEHM
FROM ICSHIPH INNER JOIN ICSHIPD ON (ICSHIPH.TRANSSEQ = ICSHIPD.TRANSSEQ) AND (ICSHIPH.DAYENDSEQ = ICSHIPD.DAYENDSEQ)
GROUP BY ICSHIPD.FMTITEMNO, ICSHIPD.ITEMDESC, ICSHIPD.STOCKUNIT, ICSHIPD.ICACCT, ICSHIPH.FISCYEAR, ICSHIPH.FISCPERIOD, ICSHIPH.CUSTNO, ICSHIPH.CUSTNAME, ICSHIPD.EXPRICEHM
HAVING (((ICSHIPH.FISCYEAR)="2007") AND ((ICSHIPH.FISCPERIOD)=5))
ORDER BY ICSHIPH.CUSTNO, ICSHIPD.ICACCT, ICSHIPD.ITEMDESC;

 

[dtmb]

Thanks, getting the folowin error(s)

SQL query cannot be presented graphically

and

Syntax error

 

[ettienne]

Where are you running the query?
Access, SQL Server, Pervasive?

 

[dtmb]

SQL Server.

 

[ettienne]

Check the syntax for SQL Server, the query I did is for MS Access. The basic query remains the same.

 

[dtmb]

Thanks