Can you tell me what is wrong with this simple sql?

[MinnisotaFreezing]

SELECT PurchaseOrder.DynacomPurchaseOrder, Sum(PurchaseOrder.Item1UnitCost) AS Expr1
FROM PurchaseOrder
WHERE (((PurchaseOrder.DynacomPurchaseOrder)>7210));

I'm writing this in Access and I get the error

You tried to execute a query that does not include the specified expression DynacomPurchaseOrder as partof an aggregate function

 

[crufty]

I am not at all familiar with Access but, as a general rule, when you select an aggregate function ( sum() in this example) with another column (PurchaseOrder.DynacomPurchaseOrder), you need to have some sort of 'group by ...' expression.

Sorry, I do not know the exact syntax for Access.

 

[tlbroadbent]

Crufty is correct. The GROUP BY syntax is as follows. I suggest posting Access SQL questions in the Microsoft: Access Queries and Access/SQL forum (forum701). In this case the ANSI and Access sysntax are identical. This is not always the case.

SELECT
PurchaseOrder.DynacomPurchaseOrder,
Sum(PurchaseOrder.Item1UnitCost) AS Expr1
FROM PurchaseOrder
WHERE PurchaseOrder.DynacomPurchaseOrder>7210
GROUP BY PurchaseOrder.DynacomPurchaseOrder;
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[MinnisotaFreezing]

Thanks for the help and hints guys, I did't know ANSI and Access SQL syntax were not always the same.

I guess my main problem was I thought the sum function did something else. Is there anyway to keep a running total for a query? That is, say im pulling up all recipts from January, and they have a total field, I would like a MonthTotal field that is a running sum of each new record in the data set. Can this be done with SQL and if so, can you show me how?

Thanks again

CJB

 

[tlbroadbent]

Here is one solution.

Select
DateCol,
(Select sum(Amount) From tbl
Where DateCol < a.DateCol
And Year(DateCol)=Year(date()))
From tbl
Where Year(DateCol)=Year(date()))

Check these links for other solutions and more info.

http://support.microsoft.com/support/kb/articles/Q208/7/14.ASP

http://support.microsoft.com/support/kb/articles/Q205/1/83.ASP

http://www.fabalou.com/Access/Queries/querycreator.htm

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm