Access 97 Loop

[Sean]

Once upon a time I used to know what I was doing in Access
but time has taken its toll and Im struggling.

I am thinking of setting up a database for helping to
forecast sales for products that my company sells.
Currently, it is done in Excel. Our key customers will
send us a file showing the last 8 wk sales and the next 12
week forecast. The file structure looks rather like this;

Item No Sales-8 Sales-7...Sales1 Forecast1...Forecast12
xxx 34 44 3 44 12

Where -8 is 8 weeks ago Sales1 is last week and so. The
file also shows the current week. I already know, I will
need to export the file into Access and using the current
week value will be able to identify the values for -1, -2
etc. I have, with advice decided that I need a file
structure in Access rather like the following;

Item WkNo QtySold Actual or Forecast DateOfForecast
xxx 25 4 Forecast 14-06-04
xxx 25 5 Forecast 21-06-04

In this way, I will be able to see if the forecasts change
week to week and show the information as a graph. My
problem is how to convert the horizontal values in Excel
and put them into the Access File Structure. I realise I
will need some form of code to loop round 20 times (8
sales weeks and 12 forecast weeks) updating each item but
I have forgotten what code to use. Can someone please
help.

 

[Peter R. Fletcher]

This really seems like a spreadsheet application. Why do you want to
do it in Access?

Once upon a time I used to know what I was doing in Access
but time has taken its toll and Im struggling.

I am thinking of setting up a database for helping to
forecast sales for products that my company sells.
Currently, it is done in Excel. Our key customers will
send us a file showing the last 8 wk sales and the next 12
week forecast. The file structure looks rather like this;

Item No Sales-8 Sales-7...Sales1 Forecast1...Forecast12
xxx 34 44 3 44 12

Where -8 is 8 weeks ago Sales1 is last week and so. The
file also shows the current week. I already know, I will
need to export the file into Access and using the current
week value will be able to identify the values for -1, -2
etc. I have, with advice decided that I need a file
structure in Access rather like the following;

Item WkNo QtySold Actual or Forecast DateOfForecast
xxx 25 4 Forecast 14-06-04
xxx 25 5 Forecast 21-06-04

In this way, I will be able to see if the forecasts change
week to week and show the information as a graph. My
problem is how to convert the horizontal values in Excel
and put them into the Access File Structure. I realise I
will need some form of code to loop round 20 times (8
sales weeks and 12 forecast weeks) updating each item but
I have forgotten what code to use. Can someone please
help.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher