Import Data into Related Tables?

Hi Guys,

Done a search of the forum on this, but some of the answers have not been concise or don't apply to my situation.

Anyway, I want to import data into the db but how do I import into multiple related tables.

FOr example:

tblCustomer

CustID
CustName
CustAddress

tblOrders

OrderID
CustID
OrderNo

tblOrderDetails

OrderDetailsID
OrderID
Product
ProductQty

How do I import the data from excel into access into the above tables? Do I import into a temp table then transfer data to the individual tables - how would I relate the data and keeping RI rules intact.

I would like to do this through code, but need an overview from someone on how to accommplish this.

Many Thanks

Andy

And what's the design of the Excel spreadsheet?

Mile-O-Phile said:

And what's the design of the Excel spreadsheet?

Click to expand...

I knew you were going to ask!

The following format is:


So that Name and Adress go to Customer table.
OrderNo goes to Orders table
Product and qty go to the orderdetails table.

But the trick is that they are related.


Thanks Mile

Andy

Is this going to be a regular thing?

This thread may also be helpful.

Mile-O-Phile said:

Is this going to be a regular thing?

Click to expand...

Not at all.

Thanks Mile

Just out of curiosity is it possible to carry this out in access from a temp table putting database design aside for the minute.

I will have a look at the spreadsheet properly, but just wondered if this could be done in access.



PS. Search Me!!

Andy

The way I do this is by adding a new column to the imported spreadsheet. It will hold the pk of the hightst level table. Create a query that selects all the columns that need to go into the customer table. Add the distinct keyword so you get only a single row for each customer. Change the query into a Make-Table query. Update the new customer table to add an autonumber primary key. Call it CustomerID. Then write a query that joins on all the new customer table fields except CustomerID to the cooresponding fields that were extracted from the spreadsheet table. Change the query to an update query and update the pk field on the spreadsheet with the value from the CustomerID field.

Once the CustomerID is added to the spreadsheet table, you can create queries that add the Distinct orders to the Order table and the OrderDetails to the OrderDetails table.

Sometimes when you get these spreadsheets you might need to add more than one column if there is more than one set of data that needs to be identitifed. For example, the spreadsheet might contain a customer type code and you want to identify all the unique values and create a table that can be used for look up's and to populate combo boxes.

Thanks for the info Pat, much appreciated.

Andy

not sure if this only applies to Access 2007 but...

i am new to access and i read Pat's answer and it seemed to make sense.

however, when i got to the part about creating an update query to update the PK field on the table, i was all confused. i kept hitting "view" in access 2007 to see how it would look. there was the PK field with nothing in it. just blank rows. i was SO confused. i spent, quite literally, 3 hours trying to figure it out. looking for all sorts of things about blank fields etc.

then, in sheer desperation i hit RUN. it worked.

so, important step there...hit RUN and hope you did it right

thanks for the tip Pat, i know it's a few years old, but it really helped me out!!!

Hmm, This is an interesting angle of attack in importing tables.

I'm trying to do the same as thing as well.

My only differece is, the top level table already has existing records and its primary key is random long interger.

If I went ahead with this method, (to generate pk first using make table query) how do I ensure that the pk generated in the preparation proccess does not conflict with the existing pk in the top level table?

As a side note: actualy what I need is to put records into the main table and get its random pk and update it to the temp table..from there than I can do other updates...