![]() |
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
|
|||
|
|||
![]()
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. |
#2
|
|||
|
|||
![]()
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. |
Thread Tools | |
Display Modes | |
|
|