1 Reply Latest reply on Jun 7, 2013 10:57 AM by rkassis

    Help to summarize multiple related tables in a database



      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


        • 1. Re: Help to summarize multiple related tables in a database

                So, what I want to do is display this layout that I attached. I want all of the records and fields from Lab, Treatment, and diagnostics to be displayed on one layout arranged by their date (each table has a field "date". Each row refers to a record (either from lab, treatment, or diagnostics. the displayed layout does not have to be editable, it can just be a copy of text from these tables. Its just needed to track patients progress. each row I colored a different color to show that the data is being pulled from one of those tables. Is there a way to do this? If you need more info let me know