I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships

However, for ease of collecting this information, we are gathering this in a flatfile in excel.(Employeeid, Fname, LName, ITskills(in multiple rows), projectName)

Our plan is to import this flatfile into MS Access into a Staging Table and then write a query to read the staging table and insert the equivalent records in the 5 tables- and creating the identifier keys systemically. (Employee, Skills, Project, employeeSkillsMapping, skillsProjectMapping)

We tried converting from flat-file to 5 tables in excel manually and importing- getting very challenging to implement the relationships. Any suggestions/ links on how to get this done- how will the keys get generated systemically in all the tables.

1 Answer 1


I don't really have any links, but I can give a general how-to of this:

After importing into Access: - A series of update scripts (or a single procedure, really) that copy the data into the appropriate tables - A purge statement clearing out the temp table for the next upload.

It sounds really simple, and it (actually) shouldn't be too difficult, but without knowing your table structure, it's hard to give true specifics.

Are the skills and projects using defined language? if so, this greatly simplifies the job.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.