sql quest

[x taol]

tbl1.....
fPum
p1
p2
p3
=============== =========
tbl2.......
fNum fDate fGoods fIn fOut fStock
1 5/6 p1 9 1 8
2 5/7 p2 7 3 4
3 5/7 p1 10 0 18
4 5/8 p3 10 9 1
5 5/10 p3 4 3 2

then,
search due : 5/8 ~ 5/11
and then, the result is below
i want to make query to below result.

fDate fGoods fIn fOut fStock
5/8 p1 0 0 18
5/8 p2 0 0 4
5/8 p3 10 9 1
5/9 p1 0 0 18
5/9 p2 0 0 4
5/9 p3 10 9 1
5/10 p1 0 0 18
5/10 p2 0 0 4
5/10 p3 4 3 2




*** Sent via Developersdex http://www.developersdex.com ***

[CDMAPoster@FortuneJames.com]

Re: sql quest

x taol wrote:

tbl1.....
fPum
p1
p2
p3
=============== =========
tbl2.......
fNum fDate fGoods fIn fOut fStock
1 5/6 p1 9 1 8
2 5/7 p2 7 3 4
3 5/7 p1 10 0 18
4 5/8 p3 10 9 1
5 5/10 p3 4 3 2
>
then,
search due : 5/8 ~ 5/11
and then, the result is below
i want to make query to below result.
>
fDate fGoods fIn fOut fStock
5/8 p1 0 0 18
5/8 p2 0 0 4
5/8 p3 10 9 1
5/9 p1 0 0 18
5/9 p2 0 0 4
5/9 p3 10 9 1
5/10 p1 0 0 18
5/10 p2 0 0 4
5/10 p3 4 3 2
>
>
>
>
*** Sent via Developersdex http://www.developersdex.com ***

tbl1
ID1 AutoNumber
fPum Text
ID1 fPum
1 p1
2 p2
3 p3

tbl2
ID2 AutoNumber
fNum Long
fDate Date/Time Format: m/d
fGoods Text
fIn Long
fOut Long
fStock Long

qryFirst:
SELECT fDate, fPum AS fGoods FROM tbl1, tbl2 WHERE fDate BETWEEN #5/8#
AND #5/11# ORDER BY fDate, fPum;

!qryFirst
fDate fGoods
5/8 p1
5/8 p2
5/8 p3
5/10 p1
5/10 p2
5/10 p3

qrySecond:
SELECT qryFirst.fDate, qryFirst.fGoods , Nz((SELECT SUM(A.fIn) FROM tbl2
AS A WHERE A.fDate = qryFirst.fDate And A.fGoods = qryFirst.fGoods ), 0)
As fIn, Nz((SELECT SUM(A.fOut) FROM tbl2 AS A WHERE A.fDate =
qryFirst.fDate And A.fGoods = qryFirst.fGoods ), 0) As fOut, Nz((SELECT
SUM(A.fIn) FROM tbl2 AS A WHERE A.fDate <= qryFirst.fDate And A.fGoods
= qryFirst.fGoods ), 0) - Nz((SELECT SUM(A.fOut) FROM tbl2 AS A WHERE
A.fDate <= qryFirst.fDate And A.fGoods = qryFirst.fGoods ), 0) AS fStock
FROM qryFirst;

!qrySecond:
fDate fGoods fIn fOut fStock
5/8 p1 0 0 18
5/8 p2 0 0 4
5/8 p3 10 9 1
5/10 p1 0 0 18
5/10 p2 0 0 4
5/10 p3 4 3 2

The problem with this is that the missing dates are, well, missing.
Maybe this will help get you started on your quest. Let me know if you
need the missing dates for certain or if these queries are incorrect.
Note: Most people in this newsgroup would argue against storing fStock
in tbl2 at all since it can be calculated.

James A. Fortune
CDMAPoster@Fort uneJames.com