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

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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).
 
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
Click to expand...

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.
 
Back
Top