How to create this query. Not easy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Hello.

yes, I do have a date field. And would like that the first lot to stay or to
be consumed is the older one.

Thae SQL query bellow is prepared for that? or instead of LAST I should put
FIRST?

Thank you very much.
Marco
 
No, well, for the running sum, only Cod, DateTime and the RunningSum should
be there, really.

SELECT a.cod, a.dateTime, SUM(b.loteQty) AS runningSum
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



and if other information is required, make an inner join on cod AND on
dateTime between this (saved) query and the initial table.



Vanderghast, Access MVP
 
Back
Top