importing an excel spreadsheet into Access and then normalizing it

[VBVines]

Does anyone have some vb code or some good ideas that will help normalize an Access database once it has imported an excel spreadsheet? Basically there are about 10 fields and it looks like it could be broken down into about four tables. aspvbwannab

 

[Databaseguy]

I wish there was code that did that. In my experience, developing the DB design and writing code are the easy part. The hard part is working with pre-exsisting data....

As far as normalization:

1. If you have data in columns, (I.E., Point of contacts, POC1, POC2, POC3, POC4...)this is a candidite. Take the data in the columns and add them to a new table, relate them in some way back to your main table using a primary key.

2. Any single record that contains multiple values must also be split into a seperate record for each table. I.E. Jim Smith,1234 Anywhere, Somewhere USA would of course be in a table with Name, CIty, State & Zip as fields.

3. Never store calculated values. You can always calculate tem laterin a query or code.

4. Anytime you see repeating data, that is an indication that the table can be further normalized. In other words, if you had the sample data in example 2 along with order IDS, then you would take all the address data out and relate it using an Address ID.

There are many books on the subject. Most likely quite a few articles on the web also.

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[New2Access]

Vbvines,

I'm learning access vb and have encountered a problem in that I do not know how to programatically import an Excel worksheet. From this thread, it seems that you have already accomplished that. Do you have any sample code that I can review.

Thanks in advance for any help that you can provide.

Tony
tmendoza1@earthlink.net

 

[JerryDennison]

As long as the imported data is in the same structure every time you can setup the necessary append queries to normalize the Excel data. This can be done "automatically" using VBA and append queries. The trick is defining the queries for splitting the flat data into a more normalized structure. Usually this isn't difficult to do, but is beyond the scope of this post to answer. Bascially, you'll want to follow the Forms of Data Normalization. I have done this myself, though the data wasn't 4 tables worth it was only maybe 2 or 3.

 

[BNPMike]

Normalising an imported table is just the reverse of doing a query that joins several tables. The principle is exactly the same.

Identify candidate keys ie what data items are uniquely identified by what other groups of data items. each of these relationships can be turned into a table.

You would probably be better to build the tables by hand rather than create them by Make Table queries. This way you can control field sizes, keys etc.

Then just do an append query from the imported table for each of the new tables.

All this can be done with queries and macros. No need (no point) to use VBA.
mike.stephens@bnpparibas.com