Import 1 spreadsheet into two tables

[Matthieu]

Is it possible to import 1 spreadsheet into two tables? I had to split a table into two- one has the call information such as phone number, date, time, sales agent, etc., the other has information about the call such as whether various legal requirements were met. The first table has the primary key as an autonumber and the second table has that as a foreign key w/ a lookup, so that's how they are joined. However, the data that was gathered is on two spreadsheets, that I can either leave separate or combine into one. I'm not sure how I can import this though, and I get a subscript out of range error trying to import the second spreadsheet into the second table. Eventually I will have it set up for a front end/back end type database so the data will go directly into my reporting database, but for now I need to import it. Any ideas on this?

[RuralGuy]

I would import them into two different tables and then manipulate them with queries and VBA.

[Matthieu]

Ya, i've got tables, queries, Forms and Reports all set up. The data is from a database that exactly matches the one I'm trying to import the data into, but I have multiple agents collecting data, and so I want to import each of their data into one master database and do reporting off of that. I haven't had time to set up the front end/back end type database, so I'm looking to import their data into my database. (each employee/agent exports their two tables at the end of the day, into excel spreadsheets) I'm trying to import their data. When I tried importing one spreadsheet table at a time, it gave me a subscript out of range error. I was thinking maybe, because of the relationships, I have to import both table/spreadsheets at once. I'm probably making this far more confusing/complicated then it needs to be.

[RuralGuy]

You've managed to loose me. The spreadsheets sound like they should be imported into two different tables.

[MAF4Fam6]

The data is from a database that exactly matches the one I'm trying to import the data into, but I have multiple agents collecting data, and so I want to import each of their data into one master database and do reporting off of that.

Sounds like you are running multiple copies of your database between you and your agents. If I am correct, and you are trying to import their tables' data into the SAME tables on your copy of the database, then the values in the AutoNumber field (Primary Key) in ALL copies of your database will eventually cause duplicate records (AutoNumber) between each other.

As RG pointed out:

The spreadsheets sound like they should be imported into two different tables.

To add on top of that...you will need to import any additional spreadsheets into their own unique tables or else you will run into further problems with duplicating records.

-RC