Creating An Access Database from an Excel Spreadsheet?

Is it best to import the data from the spreadsheet or to link it? Looking at the spreadsheet I think the data needs to be divided into different tables. Is it possible to create different tables from the new table that will be created in Access?Creating An Access Database from an Excel Spreadsheet?
Most worksheets are usually storing things like people's Names, Addresses, Phone Numbers, etc...





Such information is better stored in an MS-Access database. You would create a Primary Table that held all of the information that defines the people (name, birthdate, gender, race, etc...). Another Table would hold the physical Addresses, and yet another table for the Phone Numbers. These additional tables are secondary to the initial table, and are joined by a One-To-Many relationship (allowing any one person to have multiple entries in the other tables).





And it is best to save the MS-Excel Worksheet as a .CVS file before importing that into MS-Access. This is because Microsoft tries to do things with the data, thinking it knows what is best for you. By using a .CVS file as an in-between step, you can better control the importing of the data.Creating An Access Database from an Excel Spreadsheet?
If you link it only, then you can continue to update the information in Excel yet use Access for printing off your queries and reports etc. It depends on how well you and other users know Access as to whether you import the spreadsheet. However, once you import you can easily then split the tables though if you split the info in Excel into different sheets to start with and import or link each sheet separately aht would work though I should imagine you will still have a bit of design work to do with creating primary fields and linking the tables properly.
It depends whether you will continue to update the data, either way my preference would be to import the data and control it all from access. If updates are required this can be done from a form. To divide the data into more than one table you can simply query the data (make table query) based on the criteria you wish to divide upon.





HTH


Paul


http://www.pjasolutions.net