PDA

View Full Version : Solved: Convert To Database Format



jmenche
07-21-2005, 05:56 AM
I received a large spreadsheet that is not in database format because it would not have fit. Basically, its a spreadsheet of product sales by store. There are over 4000 stores and those fields are fine. The problem is that there are over 200 items and each is its own field. If I import this table into Access, is there a way that I can create a new table in 3rd normal form?

I have attached a sample of the data.

Thanks for any help!

Norie
07-21-2005, 06:14 AM
Are the products in columns G to HJ?

jmenche
07-21-2005, 10:12 AM
Yes! Sorry bout that. I Know that I need a new field called 'Product' and one field called 'Dollar Sales'

Thanks for any help

OBP
07-21-2005, 10:25 AM
You can create a table of the product names/codes by simply copying them and on a seperate sheet "Paste Special - Transpose" the data. That will give you a column of "products which you can then import.

Norie
07-21-2005, 12:27 PM
Jmenche

Try the attached.

You will need to have a database called Test.mdb in the root of the C:\ drive.

OBP's idea is also a good idea - the process of copying pasting could easily be automated.

jmenche
07-22-2005, 05:47 AM
WOOHOO! Thanks Norie!

The transpose wouldn't work because there are really over 4,000 stores. I copied the original data into your worksheet and the macro created a table of nearly 1,000,000 records.

Norie
07-22-2005, 06:11 AM
macro created a table of nearly 1,000,000 records.

Is that what you wanted?

As I recall there were over 200 products.

And you have over 4000 stores.

So >4000x>200 is probably going to be >1,000,000.