Covert Excel Columns to Access Table Rows

[Jules Decouls]

Hi, I have an Excel sheet for a customer with a list of products in the first column and then their stores A, B, C etc. in the next columns. They place numbers in the relevant store columns of what each store wants to order against the product row.
How do I manipulate this sheet into Access so that it will import into a table that has the following fields
Store Name
Product
Qty

 

[jdraw]

Here is a free, generic data model that relates Customers, Orders, Products.
It may help you with your overall database structure/design.

 

[plog]

You either write a macro to go line by line through your data and then write the data in another format you want to another tab. Or, you import the data as is into a temporary table in Access and then run an APPEND query to move the data from the temp table to the destination table. That query would have to be run individually for each company you had, updating it as necessary to use the correct company.

 

[The_Doc_Man]

While still in access, do a TRANSPOSE operation (make rows into columns and columns into rows) then import the table into a temp table that you can filter, rearrange field order, and otherwise manipulate.