Importing Data from Excel into mutiple tables in access

[JoeTweak]

Hello everybody...

I have an access database that is completely Normalised my problem is that the data is in asingle excel worksheet. how do do I go about importing the files so that the data will be in different tables.

Scenario:

excel file has this columns

Date - FarmerID - CropID - SampleNum - PlantHeight

for each entry in Date- FarmerName - Crop there is 50 Samples which correspond to SampleNum - PlantHeight.

Access has this tables

tblTrial
iid(autonumber) - Date - FarmerID - CropID

tblTrialSamples
iid(autonumber) - TrialID_lnk(link to tblTrial) - SampleNum - PlantHeight

So thats it how do i go about importing the data. If possible access should handle the importation, I mean maybe thru macro or VBA code it should ask for the excel file and import the data from it. I need to do this because the users have no permanent net connection to the database, so they just email the excel file to me while i do the data summary and analysis.

It quite a long question, I just hope that somebody can help me. Thanks alot in advance.

 

[Ziggy]

Split your spreadsheet into 2 spread sheets each matching that of the tables in your Database. do not include and ID column in the spreadsheets.

Now this may not work smoothly depending on the data types, try to make sure that you match the data types.

with the Tables tab selected in the DB, click the NEW button and select Import Table, click OK and change the file type to import to excel(.xls) and find your first file.

follow the steps to Import, check off column headings (make sure they are spelled the same)

select to import into an existing table and choose the corresponding one.


If errors come up force them through by accepting as it may be garbage characters, look in the Paste errors table if one is created to see what was rejected and compare your data record count or blank cells

 

[JoeTweak]

Thanks for the reply,

This will definitely import my data but the relationship is broken. Since the master table autogenerates the index number the child to use on its TrialID_lnk column. And the excel file has no way of knowing which samples is related to which Trial.

Viewing my imported data now there is no corresponding samples for each Trial on the tblTrial.

 

[Ziggy]

I'm not sure I understand. You started with an Excel spreadsheet, are the 2 spreadsheets that are created from that not of equal record counts?

Importing into Access and lettting Access generate a Primary key should give you the unique ID.

Are you trying to establish a One to Many relationship? The only thing I can think of to sync the ID is to create an ID table in EXcel for a number range equal to your highest ID number in the master table. Import that to Access along with the Master table. Create a dummy database for this.

make a Join query with the 2 tables where the Join type between the 2 ID fields and set to return all the records from the ID table. This will create blank records for the ones missing from the range.

Dump the list back to Excel, delete the records from the mastertable in your database and compact/Repare the db to reset the ID field. Then Import this table again (keep the dummy ID) and allow Access to generate the ID these will correspond to the records and should match to the original ID. Then delete the blank records. ***Add a temporary field to hold the Dummy ID field so it will import successfully.