tough summation query (to me anyway)...

[pdbowling]

Hi all.
I'm pulling lots of data from many tables and there is a field that I need to sum up instead of pull each entry. The query is large but I don't think the issue is with the contents of the query, it's with my knowledge of how to sum up certain fields in multiple table queries, so please don't try to beat your head against the entire query, I'm just curious about a particular part..the summation in the second query below that errors out.

This query works....

SELECT DISTINCT MAXIMO_V_ITEM.IN19, MAXIMO_V_ITEM.ITEMNUM, MAXIMO_V_ITEMSPEC.ALNVALUE, MAXIMO_V_ITEMSPEC.NUMVALUE, MAXIMO_V_INVENTORY.VENDOR, MAXIMO_V_COMPANIES.NAME, MAXIMO_V_ITEM.STOCKTYPE, MAXIMO_V_INVENTORY.MODELNUM, MAXIMO_V_INVBALANCES.IB5, MAXIMO_V_INVBALANCES.BINNUM, MAXIMO_V_INVENTORY.ORDERQTY, MAXIMO_V_INVENTORY.DELIVERYTIME, MAXIMO_V_INVBALANCES.CURBAL, MAXIMO_V_INVENTORY.AVGCOST, MAXIMO_V_INVENTORY.CATEGORY, MAXIMO_V_INVENTORY.BINNUM, MAXIMO_V_INVENTORY.LOCATION,
MAXIMO_V_POLINE.ORDERQTY
FROM MAXIMO_V_ITEM, MAXIMO_V_ITEMSPEC, MAXIMO_V_INVENTORY, MAXIMO_V_INVBALANCES, MAXIMO_ASSETATTRIBUTE, MAXIMO_V_COMPANIES, MAXIMO_V_POLINE
WHERE (((MAXIMO_V_ITEM.ITEMNUM)='000H2HC') AND ([MAXIMO_V_COMPANIES].[COMPANY]=[MAXIMO_V_INVENTORY].[VENDOR]) AND ((MAXIMO_V_INVENTORY.LOCATION)='KT') AND ((MAXIMO_V_ITEMSPEC.ITEMNUM)='000H2HC') AND ((MAXIMO_V_INVENTORY.ITEMNUM)='000H2HC') AND ((MAXIMO_V_INVBALANCES.ITEMNUM)='000H2HC') AND ([MAXIMO_V_POLINE].[ITEMNUM]='000H2HC'));

This one generates an error.......

SELECT DISTINCT MAXIMO_V_ITEM.IN19, MAXIMO_V_ITEM.ITEMNUM, MAXIMO_V_ITEMSPEC.ALNVALUE, MAXIMO_V_ITEMSPEC.NUMVALUE, MAXIMO_V_INVENTORY.VENDOR, MAXIMO_V_COMPANIES.NAME, MAXIMO_V_ITEM.STOCKTYPE, MAXIMO_V_INVENTORY.MODELNUM, MAXIMO_V_INVBALANCES.IB5, MAXIMO_V_INVBALANCES.BINNUM, MAXIMO_V_INVENTORY.ORDERQTY, MAXIMO_V_INVENTORY.DELIVERYTIME, MAXIMO_V_INVBALANCES.CURBAL, MAXIMO_V_INVENTORY.AVGCOST, MAXIMO_V_INVENTORY.CATEGORY, MAXIMO_V_INVENTORY.BINNUM, MAXIMO_V_INVENTORY.LOCATION,

sum(MAXIMO_V_POLINE.ORDERQTY)

FROM MAXIMO_V_ITEM, MAXIMO_V_ITEMSPEC, MAXIMO_V_INVENTORY, MAXIMO_V_INVBALANCES, MAXIMO_ASSETATTRIBUTE, MAXIMO_V_COMPANIES, MAXIMO_V_POLINE
WHERE (((MAXIMO_V_ITEM.ITEMNUM)='000H2HC') AND ([MAXIMO_V_COMPANIES].[COMPANY]=[MAXIMO_V_INVENTORY].[VENDOR]) AND ((MAXIMO_V_INVENTORY.LOCATION)='KT') AND ((MAXIMO_V_ITEMSPEC.ITEMNUM)='000H2HC') AND ((MAXIMO_V_INVENTORY.ITEMNUM)='000H2HC') AND ((MAXIMO_V_INVBALANCES.ITEMNUM)='000H2HC') AND ([MAXIMO_V_POLINE].[ITEMNUM]='000H2HC'));

I get this error (which I'm familiar with but don't know a way around).

"You tried to execute a query that does not include the specified expression 'IN19' as a part of an aggregate function."

Thanks everyone.
PB

 

[Pat Hartman]

When you use an aggregate function such as Sum() in your query, you need to include a group by clause that includes ALL other columns. Build the query in the QBE grid. Open your select query, press the sigma button. Change Group By to Sum for the quantity field.