Excel Into Access (With Correct Email Address - Muppet !!)

[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

 

[Duane Hookom]

I would vote for the few fields structure. The method I would use depends on
if this is a one time conversion (open an append query and continually
modify it for each Wk field) or write some code (loop through the fields and
run some append queries).

 

[John Vinson]

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

Very definitely the latter.

You can move data from the "wide-flat" table structure into the proper
"tall-skinny" by using what's called a "Normalizing Union Query". To
create a UNION query you need to first ask the shop steward for a new
union card... oops, sorry!... you need to go to the SQL window; type
in something like

SELECT Item, (1) AS WkNo, [Wk1] AS Sales, [Forecast Or Sales] AS Type
FROM widetable
WHERE [Wk1] IS NOT NULL
UNION ALL
SELECT Item, (2) AS WkNo, [Wk2] AS Sales, [Forecast Or Sales] AS Type
FROM widetable
WHERE [Wk2] IS NOT NULL
UNION ALL
SELECT Item, (3) AS WkNo, [Wk3] AS Sales, [Forecast Or Sales] AS Type
FROM widetable
WHERE [Wk3] IS NOT NULL
UNION ALL

<etc etc>

If this gives you a "Query Too Complex" error with all 52 weeks, split
it into two (1-26 and 27-52).

Save this Query and base an Append query upon it to populate your
(existing, empty or already loaded) tall-thin table.