Unique dynamic rolling forward update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a scheduling program and have run into a unique issue. This is
for Access 97. I have 5 tables.

Table 4 tables layed out as follows.

Table: Week1
ItemNoWk1
dteMonWk1
qtyMonWk1
dteTueWk1
qtyTueWk1
dteWedWk1
qtyWedWk1
dteThuWk1
qtyThuWk1
dteFriWk1
qtyFriWk1
dteSatWk1
qtySatWk1
Table: Week2
ItemNoWk2
dteMonWk2
qtyMonWk2
dteTueWk2
qtyTueWk2
dteWedWk2
qtyWedWk2
dteThuWk2
qtyThuWk2
dteFriWk2
qtyFriWk2
dteSatWk2
qtySatWk2
So on and so forth for tables Week3 and Week4

There are promotions for the various items we produce. What I am doing is
utilizing history as a template so if the current promo is 7 days and the
user picks an historical 7 day promo I simply pick up the historical 7 day
promo and update the current promo Week 1 with the quantities from the
history so that mfg. knows how much to produce. If the current promo is 14
days and the user picks an historical 14 day promo I simply pick up the
historical 14 day promo and update the current promo Week 1 and current promo
Week 2 with the quantities from the history so that mfg. knows how much to
produce. And so on and so forth for a 21 day or 28 day promo. This works just
fine.

Where the problem lies is if the user picks an historical promo that is less
days than the current promo. As an example let's say the current promo is 7
days and the historical promo is only 3 days. What I would like to happen is
that:

Sunday historical goes to Sunday current
Monday historical goes to Monday current
Tuesday historical goes to Tuesday current

and then

Sunday historical goes to Wednesday current
Monday historical goes to Thursday current
Tuesday historical goes to Friday current
Sunday historical goes to Saturday current

Keep in mind that I could have any combination. In other words the
historical could be a 1 day, 2 day, 3 day, 4 day, 5 day, 6 day, 7 day, 8 day,
etc. and I want the same rotation to take place.

In addition, once I have at least a 7 day historical promo then I want to
take the last 7 days of the historical and rotate them forward for the
current promo. As an example I might have a 15 day promo which crosses over 3
tables (Week 1, Week 2 and Week 3). My current promo is 28 days. I would want
the first 15 days of the historical promo to populate Week 1, Week 2 and the
Sunday of Week 3. For the remainder of the current promo I would want to take
days 9 - 15 of the historical promo and populate the current promo in the
following fashion.

Day 9 historical goes to Mon Week 3
Day 10 historical goes to Tue Week 3
Day 11 historical goes to Wed Week 3
Day 12 historical goes to Thu Week 3
Day 13 historical goes to Fri Week 3
Day 14 historical goes to Sat Week 3

Day 15 historical goes to Sun Week 4
Day 9 historical goes to Mon Week 3
Day 10 historical goes to Tue Week 3
Day 11 historical goes to Wed Week 3
Day 12 historical goes to Thu Week 3
Day 13 historical goes to Fri Week 3
Day 14 historical goes to SatWeek 3

Any help would be greatly appreciated. Thanks in advance.
 
Youre database design is flawed in that you are not efficiently storing
the data. In short, you're using 4 tables when you should only be using
1 as the data is consistent across the tables. Think of it as hiring 4
people to do 4 specific tasks when you could hire 1 and train him to do
all four tasks.
 
Back
Top