Formating for Importing into Access from Excel

[Calgary_Neil]

I have a clean database (MS Access) that I am trying to populate. The DB has 40 tables and 225 fields in total, with a lot of tables being linking tables. The data to be imported is a heavy manipulated spreadsheet with about 3200 rows. I need advice on how to go about this.

Currently I have 1 spreadsheet with all the tables and fields in it, 1 with the raw data, and use excel to calculate/lookup the required data for each field from a raw record. This sort of works until I get about 20 records done and I get some duplicating (or nearly) records. If I go over 75 records with out removing the formulas then excel slows to a stop. Since the raw data hasn’t been link together, there are consistence errors within it (typing, spelling, aberrations, etc.). Another problem is I can’t easily move/ sort/check the records without messing up the formulas or scrolling around forever.

Any Ideas or hints?
Neil T

 

[xenou]

Wow. Sounds like you need to go back to square one and fix that broken spreadsheet - all the things that are wrong with it which appear to numerous to even try to sort out in one go. For starters, I guess try to figure out why it slows downs/stops at only 75 rows and fix that. It may be that you will need to rebuild it in steps until you find what the part is that's doing the most damage.

for a database solution you need primary keys and foreign keys to relate tables. With 225 fields in 40 tables that's an average of 5 fields per table. Either you have many very small tables or a few large tables with many lookup fields. 40 tables is a lot to be handling only 3200 rows of data. It's a lot of tables for any database. The structure should be reviewed.

 

[Calgary_Neil]

Thanks for the reply Are you willing to look at either one and give a quick comment?
Neil T (Thornton at Nucleus.com)

 

[xenou]

Not really, I try to stay away from projects that sound this ugly!