Turning A horizontal Excel Sheet into A vertical Access Table

Hi,

Hope some one can help. I have been racking my brains for
ages and can not think of a solution. I must point out
that I used to be quite good at Access but I am now rather
rusty to say the least. My dilemma is this;

My job is to forecast sales of several products based on
past sales. I am sent a sheet in Excel with a column for
item number, several columns for sales and columns for
forecasts (on a weekly basis). What I am looking to do is
to export / link this file to Access but am not sure how
to design a suitable structure. Should I have a primary
key for Item No and then 52 fields representing each wk

eg Item Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Forecast or Sales
Pro1 5 8 4 1 4 8 Forecast

Or Have just a few fields

eg Item Wk No Sales Measurement Type
Pro1 1 5 Forecast

With the top way, I would have a large number of fields
and I would not know how to plot a graph with so many
fields. With the bottom way, I would not know how to turn
the excel figures from horizontal figures to vertical
figures and fit in the table struture ie.

Product Wk 1 2 3 4
Pro1 5 10 6 7 into

Product Wk No Qty
Pro1 1 5
Pro1 2 10
Pro1 3 6
Pro1 4 7

Can anyone help PLEASE

reply in most recent post

--
Duane Hookom
MS Access MVP
--

wrote in message
...
Hi,

Hope some one can help. I have been racking my brains for
ages and can not think of a solution. I must point out
that I used to be quite good at Access but I am now rather
rusty to say the least. My dilemma is this;

My job is to forecast sales of several products based on
past sales. I am sent a sheet in Excel with a column for
item number, several columns for sales and columns for
forecasts (on a weekly basis). What I am looking to do is
to export / link this file to Access but am not sure how
to design a suitable structure. Should I have a primary
key for Item No and then 52 fields representing each wk

eg Item Wk1 Wk2 Wk3 Wk4 Wk5 Wk6 Forecast or Sales
Pro1 5 8 4 1 4 8 Forecast

Or Have just a few fields

eg Item Wk No Sales Measurement Type
Pro1 1 5 Forecast

With the top way, I would have a large number of fields
and I would not know how to plot a graph with so many
fields. With the bottom way, I would not know how to turn
the excel figures from horizontal figures to vertical
figures and fit in the table struture ie.

Product Wk 1 2 3 4
Pro1 5 10 6 7 into

Product Wk No Qty
Pro1 1 5
Pro1 2 10
Pro1 3 6
Pro1 4 7

Can anyone help PLEASE