Help to summarize multiple related tables in a database
Hello, I posted a couple of topics regarding my question; however I seem to be confusing a lot of people. I have made a database for a cancer clinic in a hospital in order to store and track patients with a certain form of cancer. I currently have six tables that are related to each other. Because my question only refers to some of these tables I will only discuss those tables that I would like to use.
Table 1: main-contains general patient information that is 1:1 to each patient. ex. Name, DOB, hospital number, Gender etc. Unique key=__pk_main
table 2: Laboratory work: contains fields __fk_main (related to main table), __pk_lab (unique key) date of lab work, HGB,WBC,PLT,weight,etc.
table 3: treatment: has __fk_main and __pk_treat. contains fields that describe treatment such as type, date administered, dose, etc.
table 4: diagnoses. since each patient can have more than one subtype of this type of cancer i made a seperate table for this data. fields: __fk_main, __pk_dx, date of diagnosis, diagnosis, subtype.
Okay now, Lab work, treatment, and diagnosis are all related to __pk_main (main table) through the child key __fk_main. The tables are not related to each other directly so there are no multiple table occurances. The database is working perfectly and I love it but I have one problem. I want to display some fields from multiple records from Lab, treatment, and diagnosis and possible more tables in future in one portal.
I have been told mutiple ways to do it. 1: instead of using all those tables just have a the main table as is and another table with all of the fields from the other three tables and then use a portal (I dont like this idea but it may work) 2. Use execute sql to pool the data into a summary table and portal that table (never used sql). So I was wondering if there is another way or if someone can ellborate on some of the suggestions i have been given. Eventually I want to give a report on each patent with fields from these related tables arranged by the date the record is set to (ie diagnosis date, date of lab work, etc) I will attach a planned layout I drew out later. You will see a picture of how my database is arranged as well here. Thanks