If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Looping through data with calculations
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 04jun2010 2 a 05jun2010 4 a 06jun2010 7 a 07jun2010 4 a 08jun2010 1 b 05jun2010 4 b 06jun2010 5 b 07jun2010 3 b 08jun2010 2 b 09jun2010 1 d 03jun2010 5 d 04jun2010 7 d 05jun2010 5 d 07jun2010 6 d 08jun2010 10 Expected result: Item Date Expected supply (currently in stock  demand) a 04jun2010 10 (02=2, we need 10  no part of batch can be obtained, 102=8) a 05jun2010 0 (84=4) a 06jun2010 10 (47=3, we need 10 and we're left with 103=7) a 07jun2010 0 (74=3) a 08jun2010 0 (31=2) b 05jun2010 8 (44=0, we need 8 and we're left with 80=8) b 06jun2010 0 (85=3) b 07jun2010 8 (33=0, we need 8 and we're left with 80=8) b 08jun2010 0 (82=6) b 09jun2010 0 (61=5) d 03jun2010 20 (05=5, we need 20 and we're left with 205=15) d 04jun2010 0 (157=8) d 05jun2010 0 (85=3) d 07jun2010 20 (36=3, we need 20 and we're left with 203=17) d 08jun2010 0 (1710=7) I don't mind to do it in couple of steps. Could anybody point me in the right direction ? Thank you. 
Ads 
#2




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 05jun2010 4 : ( 8  (( 4  4) MOD 8 ) MOD 8 = 8 MOD 8 = 0*. b 06jun2010 5 : (8  ((5+4  4) MOD 8) MOD 8 = (85) MOD 8 = 3 b 07jun2010 3 : ( 8  ((3+5+4  4) MOD 8) MOD 8 = 8 MOD 8 = 0* b 08jun2010 2 : ( 8  (2+3+5+4  4) MOD 8) MOD 8 = (82) MOD 8 = 6** b 09jun2010 1 : ( 8(1+2+5+4  4) MOD 8) MOD 8 = (83) 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 04jun2010 2 a 05jun2010 4 a 06jun2010 7 a 07jun2010 4 a 08jun2010 1 b 05jun2010 4 b 06jun2010 5 b 07jun2010 3 b 08jun2010 2 b 09jun2010 1 d 03jun2010 5 d 04jun2010 7 d 05jun2010 5 d 07jun2010 6 d 08jun2010 10 Expected result: Item Date Expected supply (currently in stock  demand) a 04jun2010 10 (02=2, we need 10  no part of batch can be obtained, 102=8) a 05jun2010 0 (84=4) a 06jun2010 10 (47=3, we need 10 and we're left with 103=7) a 07jun2010 0 (74=3) a 08jun2010 0 (31=2) b 05jun2010 8 (44=0, we need 8 and we're left with 80=8) b 06jun2010 0 (85=3) b 07jun2010 8 (33=0, we need 8 and we're left with 80=8) b 08jun2010 0 (82=6) b 09jun2010 0 (61=5) d 03jun2010 20 (05=5, we need 20 and we're left with 205=15) d 04jun2010 0 (157=8) d 05jun2010 0 (85=3) d 07jun2010 20 (36=3, we need 20 and we're left with 203=17) d 08jun2010 0 (1710=7) I don't mind to do it in couple of steps. Could anybody point me in the right direction ? Thank you. 
Thread Tools  
Display Modes  

