Prev: Query prompt one column for multiple random values
Next: query to trim name field?
From: John W. Vinson on 7 Mar 2010 20:58
On Sun, 7 Mar 2010 17:20:01 -0800, clalc <clalc(a)discussions.microsoft.com>
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]
From: clalc on 8 Mar 2010 08:19
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 <clalc(a)discussions.microsoft.com>
> 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]
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: Query prompt one column for multiple random values
Next: query to trim name field?