Excel to Access - Preperation

[happymc100]

Hi Folks,
I Have a spreadsheet pulled from a maiframe report.
Column A contains a Vehicle nr.
Column B contains a Product Code.
Column C contains a Qty for the product in 'B'.

Now I want to build a database, based on this (and other) info, but if a vehicle has more than one product (upto 30), column A can have upto 30 instances of the same vehicle nr. Should I normailise this data before importing or after. How would I be best to go about this ?

I was thinking about trying to make the spreadsheet more 'Row' based rather than 'Column' based. So a vehicle would only appear on one line, with all the products and qty's on that same line also. However I cannot change the way the mainframe outputs the data. And Im not sure how to do this within Excel.

Any advice anyone could offer wouyld be gratefully received.
Im probably looking at this all wrong anyway.

Rgds
Mike

 

[KenWright]

IMO from the sound of it, your data is already how I would want it if I was doing anything with it. You don't need to make it row based here, as your output reports will handle all that for you. With your data exactly as it is now, you can simply load it straight up into Access, or even report it straight from Excel via Pivot Tables. Depending on your data though, Access may well be a more optimal app to store the data, but that doesn't stop you tapping into it with Excel using External Query options, with a Pivot Table. Don't forget, the mainframe it came from is a great big database, and hence appears to have spit out the data in database format anyway, so why change it.

Might help though if you give some idea of what the other data that you have to combine it with is like.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[fneily]

Yeah, if you had a vehicle on one row, then you would have products in separate columns. This would be against the first normal form - you would have duplicate column names (Product1, Product2, etc.)
I don't know the rest of your data, but if you kept it in Excel, you can do advance filtering, Pivot tables, and other stats on it quite quickly.