Importing Data into Linked Tables?

[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

 

[PHV]

One way is to import the spreadsheet with the wizard into a temporary table and then play with 2 append queries: one for tblItems and the other for tblTorders.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sara82]

Ok I have imported the spreadsheet into a temp table..

Now how would I create 2 append queries for tblTorders and tblItems?

 

[PHV]

What are the layout and relationships of the temporary table, tblTorders and tblItems ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeThru]

Import the orders firs to a table called orders - pull in everything thats a header. Make OrderNumber a Primary Key.


Now, create a table called orderitems. It's primary key can be ItemREf - not too important. In here you want all the item details PLUS the OrderNumber, but set to indexed (duplicates allowed)

Import the items.

In relationships, or a query, relate the two tables by OrderNumber, one to many with many on the items side. You will probably set the relationship to referential integrity.

Jobs a good 'un!

 

[sara82]

PHV:

There is no relationship among the temp table, tblTorders and tblItems, I didn't know I was supposed to create one.

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

 

[PHV]

And the layout of the 3 tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sara82]

SeeThru:

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 am unsure on how to import this spreadsheet into the appropriate tables having them linking up.

I know how to use the import feature that Access offers but that's only importing into one table. And my information in my spreadsheet is from 2 tables.

Any help will be greatly appreciated.

Thanks so much

 

[sara82]

PHV:

Layout as in what my fields are?

 

[sara82]

Ok I now have the results that I need. Thanks SeeThru. 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 needed.