An import question above the table structure

[Shag84]

Hello all,



I’m not sure how to handle the import process. I want to take all data in my Excel spreadsheets and create an Access database so I will be doing a lot of importing. The question doesn’t concern the import process but does concern table structure.

In the process of preparing & normalizing my spreadsheets to be Access ready I’ve hit a conundrum. My spreadsheet headers look something like this:

Dr Referrals (soon to be tblDrRefs)
DrName Site1-PT Site1-OT Site2-PT Site2-OT Month Year

There are actually 5 sites (soon to be 6) and 5 to 6 disciplines. So I broke out the redundant data into their own tables; tblSites, tbldiscipline, tblDrs.

If I did things correctly my new DrRefs table should look something like this:

DrRefID DrID SiteID DisciplineID RefDate RefNo.

So, if the preceding is correct, here is my question: the DrRef table would have three ID numbers from the other tables to identify the doctor, what site it was for, and what discipline it was for (PT, OT, Speech, Cardiac). Do I build my other tables using my own Primary Key ID numbers, then build the DRRef table using those numbers??

If I’m completely off the mark please let me know.

Thank you,
Don

[dcrake]

As you are recording referral details at patient level, a patient can have many referrals by different doctors for different specialties more than one doctor can refer from the same specialty. So what you want is a child table with a FK back to the patient PK, a FK back to the specialties PK and a FK back to the doctors PK. This way you can have as many or as few referrals as is necessary.

David

[Shag84]

Thank you David. I appreciate the help.

Don