Need Help! Generate query based on columns

[Walshie]

Hi All,

I have a bit of a prob I can't seem to solve:

I have a spreadsheet which lists sales for the past 52 weeks, and appears like this:

Part no Description Wk1 Wk2 Wk3 .....
05113 Door 15 20 17

I need to convert the above info into the following format so I can put into graphs etc:

Part No Week Qty
05113 Wk1 15
05113 Wk2 20
05113 Wk3 17

Anyone have any ideas? Really confusing me this one. The column headers are changeable, I can use dates if this will make any difference?

Cheers in advance
Chris

 

[Yeldarb]

You can make import the spreadsheet to a table,

then make a table using a query for the first week,

then append to the same table for each subsequent week like so

First Week Query:

SELECT Table1.PartNo, "Wk1" AS Week, [Table1]![Wk1] AS QTY INTO NewTable
FROM Table1;

Second Week Query:

INSERT INTO NewTable ( PartNo, Week, QTY )
SELECT Table1.PartNo, "Wk2" AS Week, [Table1]![Wk2] AS QTY
FROM Table1;

...and so on

Hope that helps