large import

[khurram7x]

I've a very large table to import from an Excel sheet, over 2500 rows. There is a combination of 5 repetitive fields which I want to break in a separate table. This will be the parent table. I don't want to import everything in a single table with lot of repetitive fields.

Now if i break this table in parent-child relationship, I could not find the way out how to automatically link this table with repetitive values, which will be a parent table, with child table, remaining columns from Excel sheet. I want to use Automatic ID in parent table as PK and use as FK in child table.

Because of big number of records, it is impossible to manually enter FK one by one in child table manually. How could I do this automatically, via queries, or some other method please??

This will be a repeating situation, keeping in mind the business need. Every time we setup new database, it will have almost he same scenario.

I desperately need advice please.

Thanks

 

[CJ_London]

depends how you are importing. I suggest link to the excel file, have an append query (using select distinct) to add to your parent table and create the PK's then a second query which append to your other table, doing a looking into the parent table to find the PK to use as an FK

 

[khurram7x]

...have an append query (using select distinct) to add to your parent table and create the PK's then a second query which append to your other table, doing a looking into the parent table to find the PK to use as an FK

Thank you so much CJ, it is done. I knew we could do something like this, but you gave me a clear thought.

Cheers!

 

[gemma-the-husky]

I tend to bring the data into a temporary table.

you can use an append query to append the "break-out" data to a "master table". Add an autonumber PK to this table.

Then you can link this table to the original data, and append the child data to the "child table", with the PK from the master table as the FK.