Question Importing from Excel question (1 Viewer)

[Toolman]

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

 

[BobMcClellan]

>>
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??
<<

Don,
IF you want to reference the DrRef table FROM other tables, then the
DrRefID, which is the Primary Key in the DrRef table, would be the Foreign Key used in the other tables. Think of the PK to FK relationship as one to many. There will always be ONLY 1 DrRefID in the DrRef table. It will be unique. So using this in any of your other tables will allow you to join to those tables and reference the associated DrRef data.

hth,
..bob

 

[Toolman]

Bob,

I appreciate your reply. But I'm still stumped as to how I prepare my tables to be imported. Do I use the ID's from the other tables in the DrRef table, or do I use the actual text?

Thanks,
Don

 

[BobMcClellan]

Don,
This is one simplified way to build your schema.
The objective is to group your data into logical and normalized tables.
You want to eliminate or minimize data redundancy as much as possible.
When you pull data from multiple tables, you take only the columns you actually need.
(As opposed to Select * from MyTable). That being said... plot your tables on a spreadsheet. Move all associated columns into logical tables. You should be able to
easily see where you can trim away redundancy. After you have this completed, Add
a primary Key column for each table (If it does not yet exist). Then, move the tables
in proximity to each other so that you can see which ones need to be used together for possible queries you may want to build. Once you have them separated in a way that makes sense visually,
take the Main table for each group and add FK columns (PK from the other tables) that you will need to join to them.
hth,
..bob