Solved: importing an excel file then normalising it

[philfer]

Hello,

I am using access to try to build a mangement reporting "application"

I have set up my tables for each manager etc etc

The sales, costs etc data comes in the form of an excel download from the accounting system. However it includes a lot of repeating data i.e. the manager name.

I am writing some code to copy the excel data and drop it into a table.

My question is what I do then to normalise the data

Is the best/only way to write a loop than goes through each field of the table and put in the managerid from the manager table into what will be the foreign key field in this newly normalised table.

Is there a better/easier way?

Am I overcomplicating things

Thanks
Phil

[Oorang]

Hi Phil,
It's kind of hard to answer a normalization question without some of idea of the data involved. They way you proposed is indeed one way to do it. Personally I would create an ODBC link to the file then just create make table/append queries off the link. As you get more files you can just overwrite the one you are linked to.