5 Replies Latest reply on May 22, 2013 11:06 AM by WendellNeeley

    Join Table/Reporting question



      Join Table/Reporting question


           I have three tables set up as below:


           New Exam >- Patient Demographic -< New Start


           I'm trying to use a report to get a ratio of the number of New Starts/New Exams (Fields in each table of same name).  I am assuming that I will need a join table or second table occurrence for this, but am unsure what is the best way to proceed.  The New Exam table and the New Start table have idential Patient Demographic PKs, yet I have been unsuccessful at making this work.  


           Any thoughts?  

        • 1. Re: Join Table/Reporting question

               Are these the relationships with match fields included?

               New Exam::_fkPatientID = Patient Demographic::__pkPatientID

               New Start::_fkPateintID = Patient Demogrpahic::__pkPatientID

               And you want a ratio of New Start records to New Exam records for a given record in Patient Demographic or for a set of records in Patient Demographic?

          • 2. Re: Join Table/Reporting question

                 Yes sir.  You have the basic setup.

                 What I would like to do is get a ratio of New Starts/New Exams as sorted by  Start Year and Start Month (fields taken from the Start Date field located in the New Start table)

            • 3. Re: Join Table/Reporting question

                   I'm assuming that I need a join table as the relationship between New Exam to New Start is many to many.  Hey, I guess my Patient Demographic would probably count as a join table, huh?  

              • 4. Re: Join Table/Reporting question

                     Patient Demographis is not a join table as a join table would reverse the PK to FK pairs of match fields and that's not what you have or need here.

                     The lack of any direct relationship between New Starts and New Exam is the issue. You could, going just by the relationships described here, have multiple New Start Records and Multiple New Exam records for the same record in Patient Demographics. Which Start Date in which New Start record determines which records in New Exams should be part of that ratio?

                     I can't help thinking that there are details missing on how you actually use this data. Maybe there is actually only one New Start Record for any given Patient Demographic record?

                • 5. Re: Join Table/Reporting question

                       What I ended up doing was creating a separate table  with some global fields and used them to calculate using script transferred numbers.  It works pretty well.


                       The stats are important to my profession to see how good my practice is at turning new patient examinations into new patient treatment starts.