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

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

"clalc" <(E-Mail Removed)> wrote in message
news:AD738BA6-04FD-43C7-A59B-(E-Mail Removed)...
> 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