Import excel to 3 tables keeping relationship?

[osx99]

Please can someone point me in the right direction o how to do this, I've done a few searches but can't find anything specific enough for what I'm trying to do

I have an excel spreadsheet which will be the same every time but with different data. I need to import this data into 3 access tables

1 row on the excel spreadsheet = 1 record in the database and the columns in excel match exactly the 3 tables in access in the correct order eg table 1..table 2..table 3 columns (only the primary key column for all tables is missing from excel)

How do I import the excel data to the 3 tables but keep the same primary key for each to maintain the relationship?

The primary key is an autonumber unique for each record and links the 3 table in access.

Thanks in advance.
Osx

 

[MichaelRed]

from the description, I'm not convinced that there is any point to seperating three Excel Column intop three tabes, but I'll Assume this is my err in understanding the overall process intended.

First, the EASY step is -perhaps- the mopre important issue.

It is POOR practice to directly import any 'information' into data tables. If there is a problem with a field, it requires additional processing to undo the mess which may be created. The "better way" is to import 'sttuuuffffff' into a temporary table, do SOME V&V on the temp table, and THEN (and ONLY then) move the (VALIDATED) records into your production recordset(s).

Since that is the HARD part (and the most important and critical), hte remainder is someqhat simplistic. For some (one) table, create an append query with the (UGH) AutoNumber. For the others the Autonumber created in the first append should just be a simple "Long Integer", which is tbe "foregin Key" relating back to the First appended value. If the additional table also need an AutoNumber as thrir PK, this is seperate and in addition to the FK from the first table. Therefore, the second and third appends need to relate back to the temp table and the first table to 'relate' the Autonumber generated for the first 'table' to the values included in the temp table.


Hopefully the rambling path will at least get you thinking along some line.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[fneily]

If I may add, being a purist, you would not copy an Excel spreadsheet into an Access table. Unfortunately, thousands of people do this because it's easy, a spreadsheet looks like an Access table, and there are some "experts" who say there's no harm. They are two different structures. In Excel, you have a LIST which is not NORMALIZED. Access tables should be normalized. If you normalized your Excel spreadsheet, you'll find two or more Access tables being created. Look at your column headings in your spreadsheet. Do they all related to a "primary key"? Generically, do you have repeating column headings, eg. Project 1, Project 2.
Do what you want, but I'll bet I can ask one question that you won't be able to answer due to your tables are not relational.

Neil