Bulk Imports

[Darth_Beckett]

though my actual application contains 6 tables, I will give an example with 2 to simplify my question:

Say I have a simple DB with a Company table and an Employee table....the only attributes are CompanyID (autonumber/PK) and CompanyName for the Company table and EmployeeID (autonumber/PK) and EmployeeName for the Employee table.

Now I have an excel spreadsheet with Company Names and Employee Names on a worksheet. How can I import these to both tables and keep the relationship of who works where? Keep in mind that I have 6 tables in my actual DB.

 

[GohDiamond]

Simple relationship building

You have to have a relationship between the Company and the Employee. 1 company has many employees. Put a Company Identifier in the Employee record. What's the main object that the 2nd, 3rd, 4th, etc tables are related to?
Here's a quick Example:

Table A
Company1, "Smithfield Ind"
Company2, "Waco Toys"
Company3, "Widgets Intl"

Table B
Employee1, Company1, "Johnson, Jeff"
Employee2, Company1, "Madison, James"
Employee3, Company2, "Parker, Mary"
Employee4, Company3, "Smythe, Steve"
Employee5, Company3, "Carlton, Charles"
Employee6, Company3, "Thomas, Clifford"

Company1 now relates to Employee1 and Employee2
Company2 now relates to Employee3
Cmopany3 now relates to Employee4, Employee5, and Employee6

Hope that helps

Goh

 

[Darth_Beckett]

I know that I need to have relationships between these tables. My problem is when I get external data from an excel sheet it forces me to either 1) create a new table or 2) import into an existing table. It is not letting me map the fields in the excel sheet to fields in my multiple access tables.

In reality I have an excel sheet with about 25 fields in it containing things like Name, Company, Titles, Purchase History, Addresses of contact and company, etc....and about 6500 records. I have to import this data into my 6 tables.

 

[namliam]

Save your excel file as a CSV (comma seperated file), this allows you to create an importspecification and thus select the fields to import.
Alltho this does mean you need to import 6 times for 6 tables...