Turning A horizontal Excel Sheet into A vertical Access Table

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Turning Horizontal Spreadsheet Into Access Table 1
Excel Into Access (With Correct Email Address - Muppet !!) 2
lookup or match? 5
3 Weeks Forecast-Excel 2003 4
Summing and count ? 1
Change Sort Order; Assign Number to Specific Records. 1
counting absentees 1
How to check and skip column during paste 1

Top