Importing and mapping data to various tables

[rasticle]

Hi,

This is probably easier than I'm making this out to be. If I have a data table that is denormalized and I want to import the data into a normalized database what steps do I take? What I have is very simple I have a table with facility and contact information. I want to seperate the data into existing facility and contact tables. Because there can be many facilities for one contact some contact information is repeated. How do I append that data and keep the information linked(ei keep the correct contact with the correct facility)?

I think my head is about to explode! I haven't worked with access for 2 years and now im being tasked to work with it again. =(

Thanks!

[Robertt]

rasticle,

This is typically done in module using a loop of some kind and I will assume you are using Access' autonumber.

While in the loop, you create your own unique key and assign it to a non important field in the parent record you are creating. I.e., you might have a notes column and you assign it a value of facilityname_dummyid. After inserting the record, you can then go back and search your notes column for the value you just assigned, and then grab the real primary key. Now that you have the real primary key, you can use it for your child tables.

I.e.,

 
do while loop through denormalized data one record at a time
insert into tblFacilities (fldNotes, my column list) select "facilityName_dummyid",my value list from my_denormalized_data where row=XXX
 
select fldpk from tblFacilities where fldNotes= "facilityName_dummyid"
 
update tblFacilities set fldNotes="" where fldNotes="facilityName=dummyid"
 
insert into tblChildRecords (foreignkey, my column list) select rsFacilities.pk, my value list from my_denormalized_data source
wend

HTH,

Robert