One Table Import to Multiple Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have one table that I imported to Access. This table contains fields for
two tables that are joined together. I tried creating a querry with the same
fields from the two tables and then I was going to cut and paste the new
table information at the bottom of the querry. The only information that
comes in are the fields for the first table. The others are ignored. Is
there a way of doing this.

Imported Table includes the following fields:
Issue
Issue Type
Contact
Position No
Position Description

Table 1 includes:
IssueID
Issue
Issue Type
Contact

Table 2 includes
PositionID
IssueID
Position No
Position Description

Table 1 and 2 are joined with IssueID with a one to many relationship.

Is there any way of doing this and having the fields show up in both tables?
 
Debra Ann

How do you derive the IssueID? ?by adding an Issue to Table1?

How do you know which IssueID to put in Table2? ?by selecting the issue
from your import that has the IssueID you used/created in Table1?

It may take more than one step. If your description is accurate, and there
is no "ID" field in the import data, can you confirm that there are NO
duplicates in the "issue" field. If there are, you'll need to add in a
temporary ID in the import table (and a temporary ID field in Table1) so you
can associate the IssueID from Table1 with the issue (temporaryID) in the
import table.

This gives you the connection so you can, in a second query, take the pieces
from the import table, along with the IssueID, and append them to your
Table2.
 
IssueID is an AutoNumber the is derived by adding an Issue in Table 1. I use
a form and subform with a parent-child relationship that adds the IssueID
number to Table 2. I can add an autonumber like you suggested to Table 1.
What I'm not sure of is once I add the information into Table 1 (which it
gives new autonumbers doesn't it?) how will it know how to connect the
information into Table 2.

Thanks for any advice.
 
Debra Ann

Access doesn't know. I was (perhaps poorly) describing you running two
separate queries. The first one gets the IssueID in the "parent" table.
The second query you run to fill the child table, using the IssueID that
your parent table generated. That's the reason for the tempID, so you can
keep the Issue and the IssueID connected.

And you would absolutely be looking to build a main form/subform approach to
handle data entry, but only after the bulk load it sounded like you were
trying to do.
 
Thanks, I'll try what you said.
--
Debra Ann


Jeff Boyce said:
Debra Ann

Access doesn't know. I was (perhaps poorly) describing you running two
separate queries. The first one gets the IssueID in the "parent" table.
The second query you run to fill the child table, using the IssueID that
your parent table generated. That's the reason for the tempID, so you can
keep the Issue and the IssueID connected.

And you would absolutely be looking to build a main form/subform approach to
handle data entry, but only after the bulk load it sounded like you were
trying to do.

--
Regards

Jeff Boyce
<Office/Access MVP>
Click to expand...
 
Back
Top