AnsweredAssumed Answered

importing sub tables into exisiting database

Question asked by margotjacqz on May 9, 2011
Latest reply on May 10, 2011 by margotjacqz

Title

importing sub tables into exisiting database

Post

I have a database file that includes a table for people and linked subtables for work history and education.  People has a unique field - PID - generated serial number; Ed and Hist records are linked to PID; new Ed and Hist records are created through a portal on a People layout.

information on People is being collected from different sources; one such file is similarly structured as above. I can sort out the the main table fields, but am not clear on how best to handle the linked records.  In importing the people I did think to add a field for and keep the orginal UniqueID, so incoming related records might get connected, but then I want to clear up the redundancies.

In briefest form, the critical bits:

Target database has Table=People including it's own <PID> unique field,  and <ImportID>, from the source records. 

                       and  Table=History, with a <PID_fk> for each job record (with its own ui)

Source database has Table=People including unique field <ImportID>

                       and   Table=History with an <ImportID_fk> for each job record (with it's own ui)

I think my question is What is best way to get ImportID lined up with and then replaced by the existing PID. On the theory I don't want two keys for the same purpose in a join.

i considered reusing the Imported ID as the PID, but that seems wrong. not only does it disturb my sense of order to have different serial id series, I am guessing it won't work to just rerun the numbering later once all the data is in. <s>. 

I could envision importing as a new table and then doing a look up / import from one table to the next (how)?  but that seems a long way round. So throwing it out there for thoughts on processes.

tnx

Outcomes