calculated field - difficult

On Sun, 7 Mar 2010 17:20:01 -0800, clalc
wrote:

Sorry I used another name for QTY that's why I was getting this message. I
fixed it now, but I'm still not getting the right numbers.


If you would like help solving the problem, please post the current SQL of the
query, and an example of the data in your table, the wrong answer you're
getting, and the answer you desire to get.
--

John W. Vinson [MVP]

Here are the tables:
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 Stock
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)

Here is SQL which does not give the results as above:
SELECT Demand.Item, Demand.Date, (SELECT IIF((Stock.[Qty] -
Sum([XX].[QTY])) 0,0, (Stock.[Qty] - Sum([XX].[QTY]))*-1) FROM
Demand AS [XX] WHERE [XX].Date = Demand.Date AND [XX].Item
= Demand.Item) AS ToOrder
FROM Demand LEFT JOIN Stock ON Demand.Item = Stock.Item
ORDER BY Demand.Item, Demand.FillDate;
Would you have any suggestions ?


"John W. Vinson" wrote:

On Sun, 7 Mar 2010 17:20:01 -0800, clalc
wrote:

Sorry I used another name for QTY that's why I was getting this message. I
fixed it now, but I'm still not getting the right numbers.


If you would like help solving the problem, please post the current SQL of the
query, and an example of the data in your table, the wrong answer you're
getting, and the answer you desire to get.
--

John W. Vinson [MVP]
.