View Single Post
  #2  
Old June 7th, 2010, 02:34 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Looping through data with calculations

Seems that the following would do (assuming the data is as supplied it, it
that it was not an over simplification):



SELECT r.product,
r.datestamp,
LAST(r.quantity) ,
(LAST(m.quantity) - (SUM(s.quantity) - LAST(i.quantity)) MOD
LAST(m.quantity) ) MOD LAST(m.quantity) AS qty
FROM ( tableb AS r INNER JOIN tableb AS s
ON r.product = s.product
AND r.dateStamp = s.dateStamp )
INNER JOIN currentlyInStock AS i
ON r.product = i.product )
INNER JOIN tableA AS m
ON r.product = m.product
GROUP BY r.product, r.dateStamp

ORDER BY r.product, r.dateStamp





The expression

(LAST(m.quantity) - (SUM(s.quantity) - LAST(i.quantity)) MOD
LAST(m.quantity) ) MOD LAST(m.quantity)

does all the job.

Note that for a given product, LAST(m.quantity) is the value from tableA.
So, for illustration, for *product B*, that values is 8, and for product B,
that is reducible to :

(8 - (SUM(s.quantity) - LAST(i.quantity)) MOD 8)) MOD 8


About LAST(i.quantity), again, for a given product, that is the constant
from table currently in stock C, so, for product B, that is 4, ie. the
initial in stock value.

( 8 - ((SUM(s.quantity) - 4) MOD 8 ) MOD 8


Now, for a given product, and a given dateStamp (GROUP BY r.product,
r.dateStamp ), the expression SUM(s.quantity) returns the cumulative
request, up to that date, for the given product.

b 05-jun-2010 4 : ( 8 - (( 4 - 4) MOD 8 ) MOD 8 = 8 MOD 8 = 0*.
b 06-jun-2010 5 : (8 - ((5+4 - 4) MOD 8) MOD 8 = (8-5) MOD 8 = 3
b 07-jun-2010 3 : ( 8 - ((3+5+4 - 4) MOD 8) MOD 8 = 8 MOD 8 = 0*
b 08-jun-2010 2 : ( 8 - (2+3+5+4 - 4) MOD 8) MOD 8 = (8-2) MOD 8 =
6**
b 09-jun-2010 1 : ( 8-(1+2+5+4 - 4) MOD 8) MOD 8 = (8-3) MOD 8 = 5**




*: you use 8 instead of 0, but technically, it is useless to bring a new
batch, and to not use it at all. Sounds that 0 is more appropriate, isn't
it?
**: you typed 0 as result, but the arithmetic expression you supplied
returns the announced value (not zero).




Vanderghast, Access MVP



"clalc" wrote in message
...
I have to loop through the data from one table (demand) and calculate total
supply expected depending on the data from the other tables, here is
example
and expected result:
Table A - (supply batches)
Item Quantity
a 10
b 8
c 12
d 20
Currently in stock C
Item Quantity
a 0
b 4
c 2
d 0
Table B - (demand)
Item Date Quantity
a 04-jun-2010 2
a 05-jun-2010 4
a 06-jun-2010 7
a 07-jun-2010 4
a 08-jun-2010 1
b 05-jun-2010 4
b 06-jun-2010 5
b 07-jun-2010 3
b 08-jun-2010 2
b 09-jun-2010 1
d 03-jun-2010 5
d 04-jun-2010 7
d 05-jun-2010 5
d 07-jun-2010 6
d 08-jun-2010 10

Expected result:
Item Date Expected supply (currently in stock - demand)
a 04-jun-2010 10 (0-2=-2, we need 10 - no part of batch can be
obtained, 10-2=8)
a 05-jun-2010 0 (8-4=4)
a 06-jun-2010 10 (4-7=-3, we need 10 and we're left with 10-3=7)
a 07-jun-2010 0 (7-4=3)
a 08-jun-2010 0 (3-1=2)
b 05-jun-2010 8 (4-4=0, we need 8 and we're left with 8-0=8)
b 06-jun-2010 0 (8-5=3)
b 07-jun-2010 8 (3-3=0, we need 8 and we're left with 8-0=8)
b 08-jun-2010 0 (8-2=6)
b 09-jun-2010 0 (6-1=5)
d 03-jun-2010 20 (0-5=-5, we need 20 and we're left with 20-5=15)
d 04-jun-2010 0 (15-7=8)
d 05-jun-2010 0 (8-5=3)
d 07-jun-2010 20 (3-6=-3, we need 20 and we're left with 20-3=17)
d 08-jun-2010 0 (17-10=7)
I don't mind to do it in couple of steps. Could anybody point me in the
right direction ?
Thank you.