Import Nightmare

[Superock]

Hi all,

I have been asked to create a simple database for tracking assets. This would have been a fairly simple task if not for the data that I have to import into the dB that I still have to create.

Before I create the dB I need to figure out how to get the data into it.

So far I have 2 options.
1. Import the excel data as a flat file and then split this up into seprate tables.
2. Create dB and manualy populate dB.

The problem with the excel spreadsheets are that there are multipule records for a single user ( have included a sample .xls) and I am not sure if I can import the data and maintain integrity.

Any help would be appreciated.

 

[ejstefl]

The easiest way is do this is to create all of your tables, etc. in your database. Then import the Excel file into a temporary table, and use append queries to get the data to where it needs to go.

 

[mresann]

Before you can do ANY importing, you have to design the database. While looking at your excel sheet, I could not really figure out how the data entry has been handled in the past. I recognize it as an asset handling spreadsheet, but some of the fields aren't clear to me. In any case, you have to design the tables in the Access Database not only to house the data, but to properly associate key fields as well. I would check out the Northwind database for some sample tables which correspond to what you are trying to do.

Once the db is designed, since this is to be a one-time import, I recommend importing the spreadsheet into a single table in your new database using the wizard function ("Files", "External Data", "Import"). Once you do this, then it's much easier to query the table than to query or link an excel sheet. The next step will be to matching the datatypes of the data to import to the datatype of the tables you have created. Finally, create the queries to update the tables in the database. Once all data is properly assigned to the tables, you can delete the import table.