I have 3 spreadsheets that I want to import into Access but I"m having a problem figuring it out. I'm not even sure it can be done the way I want to do it.
One Spreadsheet looks like this:
StoreNum, SKU_1, SKU_2, SKU_3, ..., SKU_1000
1, 0, 100, 25, ..., 1
2, 5, 250, 15, ..., 5
3, 78, 25, 12, ..., 15
How can I import this so I have StoreNum, SKU, Qty?
The others are easy for me to understand
StoreNum, Address1, Address2, City, State, Zip
and
Sku, Description, Notes, Size
I think you want to store the spreadsheet data in Access like this ...
StoreNum SKU Qty
1 1 0
1 2 100
1 3 25
1 1000 1
2 1 5
If that's what you want, keep reading. If it's not what you want, please clarify what you want.
In Access, create a link to the Excel worksheet and name that link ExcelSource.
Then create a SELECT query similar to this:
SELECT
StoreNum,
1 AS SKU,
SKU_1 AS Qty
FROM ExcelSource
UNION ALL
SELECT
StoreNum,
2 AS SKU,
SKU_2 AS Qty
FROM ExcelSource
UNION ALL
SELECT
StoreNum,
3 AS SKU,
SKU_3 AS Qty
FROM ExcelSource
UNION ALL
SELECT
StoreNum,
1000 AS SKU,
SKU_1000 AS Qty
FROM ExcelSource;
If the result set from that SELECT query gives you what you need, convert it to an "append query" to store those data in a table named YourTable. The SQL for that query will be the SELECT SQL preceded by an INSERT INTO ... section:
INSERT INTO YourTable (StoreNum, SKU, Qty)
SELECT
StoreNum,
1 AS SKU,
SKU_1 AS Qty
FROM ExcelSource
UNION ALL
SELECT
StoreNum,
2 AS SKU,
SKU_2 AS Qty
FROM ExcelSource
UNION ALL
SELECT
StoreNum,
3 AS SKU,
SKU_3 AS Qty
FROM ExcelSource
UNION ALL
SELECT
StoreNum,
1000 AS SKU,
SKU_1000 AS Qty
FROM ExcelSource;
If your Excel worksheet includes 1000 SKU columns, create a series of smaller append queries, each of which uses a manageable subset of those 1000 columns.
This could also be done with VBA code rather than a query. I don't want to lead you through that option because I don't even know if I'm on the right track here.
When you open your Access database click;
File
Get External Data
Import
And follow the Wizard Steps.
A few caution points. Make sure that all of your excel sheets have the same headers, and do not contain any Null, DIV, or any other type of errors. Happy importing!!!
