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?
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
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.
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.
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.
I have attached screen shot showing relationship. Should "Active" variable occur on initial table -- with patient demographics -- or only on Month table. Thoughts?
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.