View Single Post
  #1  
Old June 4th, 2010, 08:52 PM posted to microsoft.public.access.queries
clalc
external usenet poster
 
Posts: 16
Default 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 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.