7 Replies Latest reply on Dec 17, 2012 8:13 AM by philmodjunk

    Appreciate advice creating a report of total active patients managed in a particular month

    BramRaphael

      Title

      Appreciate advice creating a report of total active patients managed in a particular month

      Post

           I have a database of patients managed by my specialized medical program.  There are start dates and end dates for each patient, as I only manage them for a particular amount of time.  

           I am looking to create a report listing the number of patients I'm managing each month.  For example, July 2012 there were 84, August 12 there were 86, etc...  I've already created a report with number of patients starting treatment each month.  Hoping to add another column in report listing total number of active patients.

           Appreciate any advice?

           BPR

        • 1. Re: Appreciate advice creating a report of total active patients managed in a particular month
          philmodjunk

               Can you describe the design of the report you have to which you want to add this column? What you want may be simple or complext depending on the design details of your database.

               Would "active" patients for December 2012 be patients with a start date on or before December and an end date on or after December?

          • 2. Re: Appreciate advice creating a report of total active patients managed in a particular month
            BramRaphael

                 Sure, happy to clarify.

                 Variables: MRN (patient's ID number), Dateassume (date that a particular patient starts), Enddate (date that said patient ends care).  I should add, 1/1/1901 is entered with patient has not ended care with us.

                 Patients can be active in the month of October 2011 by satisfying the following criteria:

                  

                 Case (
                 DATEASSUME  <  Date ( 10 ; 1 ; 2011 ) and ENDDATE  = Date (1; 1; 1901) and ENDNOTAPPLIC = "Not applicable"; 1;
                 DATEASSUME  <  Date ( 10 ; 1 ; 2011 ) and ENDDATE   ≥  Date (10; 1; 2011) and ENDDATE   ≤  Date (10; 31; 2011); 1; 
                 DATEASSUME   ≥   Date ( 10 ; 1 ; 2011 ) and DATEASSUME ≤ Date ( 10 ; 31 ; 2011 ); 1;
                 DATEASSUME  <  Date ( 10 ; 1 ; 2011) and ENDDATE   > Date (10; 31; 2011); 1; 0
                 )
                  
                 Or put in words: They started care before October 1 and still managed, they started care before October 1 and ended care in October, they started care sometime in the month of October 2011, or they started care before October 1 and ended care after October 31.  All other conditions mean they were not a patient in October 2011.
                  
                 However, I'd like to be able to automatically calculate patients managed every month within a report.  
                 So, it'll tell me how many patients are managed for October 2011, NOvember 2011, etc...
                  
                 Any ideas?
                  
                 Thanks, BPR
            • 3. Re: Appreciate advice creating a report of total active patients managed in a particular month
              philmodjunk

                   I understand what you want and your posts confirms my assumptions about that, but with useful added detail. But I asked you also to tell me what you have in terms of your current report as there is more than one way to produce that report. If I knew what you have now, I could determine whether or not a few changes might make it possible to just add to that report or if you'll need to start over from a "blank page".

                   Using the "blank page" approach, the key detail that I see here is that the same patient record can be counted for more than one month. An added complication is in the fact that you have an EndDate of 1/1/1901 for currently active patients.

                   I think you'll need this calculaiton field to start:

                   cMonthAssume: DateAssume - Day ( DateAssume ) + 1

                   This will compute a date for the first day of the month for the date entered into DateAssume giving you a common value for all assume dates falling in the same month.

                   Then we need cMonthEnd:

                   If ( EndDate = Date ( 1 ; 1; 1901 ) ; Date ( 1 ; 1 ; 5000 ) ; EndDate - Day ( EndDate ) + 1 )

                   Now if you create a table where you have one record for each month, with a date field with the date for the first day of the month, you can set up this relationship:

                   MonthsTable::MonthDate > Patients::cMonthAssume AND
                   MonthsTable::MonthDate < Patients::cMonthEnd

                   will match only to active patients for the month specified in MonthDate.

                   Count ( Patients::MRN ) will count the active patients for that month, or you can refer to a summary field from patients that "counts" a never empty field in patients. A list view layout on MonthsTable can then produce a column counting active patients for each month.

              • 4. Re: Appreciate advice creating a report of total active patients managed in a particular month
                BramRaphael

                     Thanks.  I followed you up until the last paragraph.  I created variables cMonthAssume and cMonthEnd within patient table.  Works fine.  I created another table called "MonthsTable" and set up relationships.  Within MonthsTable, I entered first day of each month.  Second variable was calculation with count of MRN in first table.  Nothing happens.  I really appreciate your advice.  Almost there it seems.

                     BPR

                • 5. Re: Appreciate advice creating a report of total active patients managed in a particular month
                  philmodjunk

                       And what is the relationship to go with this?

                       What you have looks correct, but issues in the data or the relationship might keep the calculation from correctly computing a count of the records in the related table.

                  • 6. Re: Appreciate advice creating a report of total active patients managed in a particular month
                    BramRaphael

                         I have attached screen shot showing relationship.  Should "Active" variable occur on initial table -- with patient demographics -- or only on Month table.  Thoughts?

                         BPR

                    • 7. Re: Appreciate advice creating a report of total active patients managed in a particular month
                      philmodjunk

                           The relationship looks correct.

                           What data do you have in MonthTable?

                           You need one record for every month of the year and the correct first of the month date in MonthDate in each of these records. This is something that you might want to create with a script to reduce the amount of data entry needed.