Dlast Function in Subquery

[Raul Sousa]

I have this kind of table

Code Date Qty Value Amount
A N 1 5 5
A N+1 -2 Y
B N 2 3 6

The first Qty for a given code is always positive.
It is ordered by code them by date.

If Qty is negative I want Value (Y) to be Equal to the preceding value (5).
In other words if Qty is negative, Value (Y) must be equal to the value
corresponding to the last positive Qty (5).

I’m thinking of using DLast function in a subquery. More or less like this
Dlast(“valueâ€;â€Tableâ€;Code=Code and Date<date)

The problem is that I have not enough knowledge to make it work. Any hint
will be most welcome.

 

[Michel Walsh]

Hi,


SELECT a.code, a.date,
iif(a.qty>=0,
a.qty,
( SELECT LAST( b.qty )
FROM mytable As b INNER JOIN mytable As c
ON b.Code=c.Code
WHERE b.Code=a.Code
AND b.Date < a.Date
AND c.Date < a.Date
GROUP BY b.Date
HAVING b.Date = MAX( c.date)
) )

FROM myTable As a




the iif makes the test about the negativity of the actual quantity, if
negative, we retrieve the qty that matches the record with the maximum date,
but still with a date less than the actual date being considered, and all
that, for the same "code" . Note that in this particular case, LAST can be
replace with MIN, MAX or FIRST, since it is just a matter to aggregate ONE
value (from ONE record).



Hoping it may help,
Vanderghast, Access MVP

 

[Raul Sousa]

Thank you.
I will Try your sugestion.
You anwser was very helpfull to me.