AnsweredAssumed Answered

importing sub tables into exisiting database

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


importing sub tables into exisiting database


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.