Re: How to create this query. Not easy

Sure, if you have a field that gives a unique indication about which lot to
use first, such as a date_time field, implying that the oldest lot
(accordingly to the value of the date_time field) is to be used first. It is
then a matter to make a running sum until the quantity you asked is equaled
or exceeded:


Cod LoteNo LoteQty DateTime RunningCodQty
12 A 20 2001.01.01 20
12 B 17 2001.02.02 37 ( = 20 +
17)
12 C 19 2001.03.03 56 ( = 37+
19)



so, for a qty=35, second row, with the lowest RunningCodQty >= 35, tell us
we need the first two rows ('first' and 'second' refereeing to an order
done accordingly to the date_time field values).


To get a running quantity, someone can use a join like:

SELECT a.cod, LAST(a.LoteNo), LAST(a.LoteQty), a.dateTime, SUM(b.loteQty)
FROM myTable As a INNER JOIN myTable As b
ON a.cod = b.cod AND a.dateTime >= b.dateTime
GROUP BY a.cod, a.dateTime





Hoping it may help,
Vanderghast, Access MVP

"Marco" <Marco@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FBC23E9E-EE19-41D7-A9D8-E7CC1D024345@xxxxxxxxxxxxxxxx
Hi. I nedd a query that eliminates or update records and the crietria is
the
diferent of two columns.

I have a tabel with records like this:

Cod LoteNo LoteQt QtNeeded
12 A 20 15
12 B 20 15

To have this:
Cod LoteNo LoteQt QtNeeded
12 A 20 15




In that example I have the an item code (12) with two Lotes, A and B but I
only need to consume 15 so I would like to remover the second line because
the quantaty that I need is less than the one that I have.

But could also happens this:
(if not possiblem, well)

Cod LoteNo LoteQt QtNeeded
12 A 20 30
12 B 20 30

I would like to transform into this:
Cod LoteNo LoteQt QtNeeded
12 A 20 20
12 B 20 10

is it possible to create a query like this? or is impossible?

Regards in advance,
Marco





.