Importing Data into Linked Tables? (1 Viewer)

[sara82]

I'm trying to perform a massive data dump. I have about 1,000 records that need to be added into my database. This is coming off an Excel spreadsheet.

I don't know if this is possible:
I have a table, tblTorders, which is linked to the table, tblItems.
When I open the tblTorders, I can click on the plus sign to expand and see the related data in tblItems.

How can I import data to populate the fields in both of these tables? I can't use the import function because you can only select to import it into one table.

Do I do it through a query.... I'm not sure where to go on this one.

Thanks so much

 

[ScottGem]

You need to let us know more about the spreadsheet and how the data relates to your tables. But if the item # is in the record, you should be able to do it.

 

[Pat Hartman]

When the data in the spreadsheet represents a flattened 1-many relationship, you need to run multiple append queries. One for each table. Be sure to use the distinct key word when selecting the data to append to the 1-side table to prevent duplicates. To get the autonumber value for the foreign key, you'll need to join the 1-side table to the spreadsheet table on ALL the fields in the 1-side record except the autonumber.

 

[sara82]

PatHartman:

How about I create multiple append queries to obtain the results that I need?

The relationship between tblTorders and tblItems is as follows:

From tblTorders the primary key is TONumber so there is a one to many relationship from tblTorders and tblItems with TONumber as the primary key in tblTorders.

I have about 14 fields in tblTorders and about 10 fields in tblItems.

My excel spreadsheet has 4 fields from the tblTorders and 3 fields from the tblItems.

I don't know how to use append queries.

Any help will be greatly appreciated. Thanks!

 

[sara82]

Ok I now have the results that I need. I created 2 temporary tables which I imported my spreadsheet data into. I created 2 append queries. From my temp tables I appended to the my "real" tables and I now have all the info that I need.