calculating value in a query

[clalc]

how would i build a function that would calculate value which is basically
aggregate summary per group ? Here is an example:
Table Demand
Item Qty Date
aaa 10 03-mar-2010
aaa 7 05-mar-2010
aaa 5 12-mar-2010
ccc 8 06-mar-2010
ccc 12 09-mar-2010

Table On Hand
Item Qty
aaa 12
bbb 9
ccc 6

the result should be table Net Demand
Item Qty Date (how to get column Qty)
aaa 0 03-mar-2010 (12-10, left 2)
aaa 5 05-mar-20 (7-2, left 0)
aaa 5 12-mar-2010 (5-0, left 0)
ccc 2 06-mar-2010 (8-6, left 0)
ccc 12 09-mar-2010 (12-0, left 0)
Any suggestions appreciated

 

[vanderghast]

SELECT a.item,
a.date,
Nz(SUM(p.qty), 0) AS upToExcludingThisOne,
LAST(a.qty) +Nz(SUM(p.qty), 0) AS upToIncludingThisOne,
iif( LAST(stock.qty) > upToIncludingThisOne, 0,
iif( LAST(stock.qty) > upToExcludingThisOne, upToIncludingThisOne -
LAST(stock.qty),
LAST(a.qty)
)
) AS netDemand

FROM (demand AS a LEFT JOIN stock ON a.item = stock.item)
LEFT JOIN demand AS p ON p.item = a.item AND p.date < a.date

GROUP BY a.item, a.date



(from a discussion in microsoft.public.access.queries)



Vanderghast, Access MVP