Safety Stock

[gatarossi]

Dear all,

I have two tables, and I need to calculate the final stock for periods
of time:

The first table contains: the item code, the period and the
percentual;
The second table contains: the item code, the period and the quantity;

Table1: Safety Stock

item_code period percentual
xxxx1 1 1
xxxx1 2 1
xxxx1 3 0,5

It means that I want 2,5 months of safety stock for this item...

Table2: Stock

Item_code period type quantity
xxxx1 200701 INV 100
xxxx1 200702 INV 110
xxxx1 200703 INV 120
xxxx1 200704 INV 130
xxxx1 200705 INV 140
xxxx1 200706 INV 150

INV means the invoice quantity of the month...

Then I'm trying to do a INSERT INTO consult to put the final stock in
the table stock...

The final stock of
200701 is (1*110) + (1*120) + (0,5*130) = 295
200702 is (1*120) + (1*130) + (0,5*140) = 320
200703 is (1*130) + (1*140) + (0,5*150) = 345
200704 is (1*140) + (1*150) + (0,5* 0) = 290
..
..
..
The period of table safety stock means that I need the percentual of
safety stock for the actual period in the table stock + this period...

Then the consult will insert into the table stock this data:

Item_code period type quantity
xxxx1 200701 FST 295
xxxx1 200702 FST 320
xxxx1 200703 FST 345
xxxx1 200704 FST 290
..
..
..
As I'm a begginer user of access, I have no idea how to do it!!! Could
anybody help me, please!!!!

Thanks a lot!!!

André.