Need some ideas for some advanced reporting for my database
I currently have my database mostly set up to my liking and am trying to figure out the best ways to extract the relevant statistics and data that I need from it. This is for my orthodontic practice and I'm attempting to keep track of which patients go into treatment, what sort of treatment they take and how that relates to the individual referring doctors. I also am looking at the health history of these patients, marketing visits to referring doctors and also the birthdays of the employees of referring doctors. In a nutshell, you could say it is the ultimate marketing database for what I do.
Tables (one to many as you go down the list):
- Doctor Information- First name, last name, Portals to Patient Info, New Exam Info, New Start Info, Debond Information
- Patient Information- Birthdate, Insurance type, primary office, etc., Portals to New Exam Info, New Start Info and Debond Info
- New Exam Info- Exam Date, Exam Outcome, Portal to New Start Info
- New Start Info- Start Date, Fee, Treatment Type, Discount, other relevant financial data
- Debond Info- Finish date, financial information, Early removal of braces, etc.
I won't get into the other tables just yet as I haven't really fleshed that part out yet and it should be infinitely easier than what I'm dealing with right now. What I currently have is a couple of layouts to look at my global statistics that I care about, such as average fee, percentage of new exams that start, etc. and I have, through portals, put all of the information that I wish to see on the Doctor Information page, thus limiting the data observed to each doctor. This is important to me as I'd like to see where to focus my marketing.
What I'd like to do now, with both the global statistics and also the portal related data on the doctor's data page, is to divide these up in to defined date ranges, for instance all the months of 2011. I'd like for this data to be all on one layout so that I can both easily print it and also to see how the values change over time.
Example: Doctor- Johnny Johnnerson
January 2011 February 2011 March 2011 April 2011
New Start %
Right now I can see all this information as a whole, for the entirety of the database's dates, but I have to figure out a way to easily narrow the date range in a reapeatable fashion so that I can see trends. I've tried to do a date limitation on the portals centered on the examination date (New Exam Table), but it doesn't appropriately cull out the dates, i.e. I get the same numbers with the portal filtere as I do when I don't filter it.
Can anyone help me with some ideas to make this work? I suppose that I could do individual equations for each doctor, but that would be a real pain.