Greetings.
For the past 10 years, I have been using a database that I created to keep track of patients. However, when I created this, I was using MS Access 2003 and more concerned about the front end (the form used to enter the data) rather than the data itself and the relationships, etc. I upgraded to Office 2016 and decided it was time to learn more about Access since I was designing all these queries and reports, most of which couldn't be used because there were actually no (i.e., zero) relationships among the tables I had. I've picked up the Access 2016 Bible and have been reading through this, and I've realized that what I had was a complete mess.
So, I've started from scratch.
Here's the basic need. I am a surgeon. For research purposes and for personal record keeping, I log all the patients I operate on. A patient and his/her characteristics would be the main piece of data. However, I may operate on the patient more than once and do different procedures on that person. There are right-sided and left-sided procedures.
In the database I have attached, I have 6 tables: tbl_patients (the main record), tbl_surgeries (the surgery event), tbl_icd10 (the diganosis codes -- in the example there are four, but in the full table there will be about 1000 or more), tbl_CPT (the 5-digit codes for each procedure; the mature table will have about 1000 entry as well), tbl_category (simple diagnosis categories), tbl_docs (the list of doctors who send me patients).
There are no actual people in the skeleton db. Here's the relationship. I figured the one-to-many would be appropriate since a patient could have many surgeries.
Here is the tbl_patients in action.
Anyway, I would be thankful to anyone who could give me a few pointers about my table normalization, the relationships, whether I should use "lookup wizards" for inputting data or whether I should allow multiple entries for fields (ICD10, CPT -- in reality, that's true for any patient/procedure). I can already tell that a lot of people do not recommend lookups on the table level...
I tried to upload the database, but it exceeded the 500 kb limit.
Here is the link to it on Google Drive.
Thank you very much.