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