Report on Previous Data

[shelby55]

Hello

I am using CR9 accessing an MS-Access 2002 database on a computer with Windows XP/SP2.

I have a patient database where chart number is the patient identifier (each patient has a number that is used for all of their visits) and account number which is the visit identifier (unique number - no duplicates in database). There is also the readmit code which identifies status of visit i.e. if '2' then current visit is within 7 days of last visit; if '3' then current visit is within 8 to 28 days of last visit. There is also a field called "CMG" which I want to report on for the previous visits. Note that I am able to report on this (thanks to Tek Tippers) by chart number using previous(CMG) but want to report on by grouping in previous CMG.

The problem arises by selection of time frame so I'm wondering if I need to use a subreport. What I mean by the above statement is that if the timeframe chosen is Apr 1 2004 to Jun 30 2004 an admission of April 7 could have '3' as the readmit code meaning the previous admission was between March 10 and 30. However, the report isn't for that timeframe so this previous admission can't be reported on.

Any ideas how I can do this? Thanks.

 

[MrBillSC]

Your select formula will have to include the previous 28 days:
{table.visitdate} >= {?BeginDate} - 28

If you want to print the information from records prior to {?BeginDate} if the next record contains a CMG value, conditionally suppress the detail section based on the visit date and:
Not (Next(table.CMG} in ["2","3"])

I think this should work.
MrBill

 

[shelby55]

Hi MrBillSC

Your reply helps with the timeframe but I can't seem to group on the "previous" CMG. The formula for the field is:

If {Data.Readmit} in ["2","3"] and previous({Data.ChartNo}) = {Data.ChartNo} then previous({Data.CMG})

(thanks to SynapseVampire and Lance Bass for assistance with the initial request which resulted in the above formula).

Note that unless I group on chart number, the above doesn't work. I'm still confused and require further assistance - can you help?

Shelby

 

[shelby55]

Hi MrBillSC

The timeframe for the report is a range i.e. {DisDate} in Date (2004, 04, 01) to Date (2004, 06, 30) so how would I incorporate the -28 days?

Shelby

 

[MrBillSC]

If your date range is hard coded, simply change the beginning date:
{DisDate} in Date (2004, 03, 03) to Date (2004, 06, 30)

MrBill

 

[shelby55]

Thanks MrBillSC. I figured out this date issue but I still can't report as I wanted to in grouping on this "preCMG" formula. Any ideas on that?

 

[synapsevampire]

As for the preCMG, you might alter the record selection formula to also pull in that data, or you can use a subreport as well, but the performance will be worse.

If you opt for pulling both the current data and the preCMG, you'll haev to use conditionals on all summary data to only pull what is appropriate for the totals required for each set of dates.

I think that I'd need to see example data and how you derive the other periods data as "Apr 1 2004 to Jun 30 2004 an admission of April 7 could have '3' as the readmit code meaning the previous admission was between March 10 and 30" wasn't immediately clear to me.

-k

 

[shelby55]

Hi SynapseVampire

Sorry for the confusion, I'll try to explain further. A patient has a chart number which is their "forever" number and an account number which is visit specific and unique in the database.

When a patient is admitted to hospital, if the current admission date is within 7 days of the last visit discharge date, they have a readmit code of '2'. If the current admission date is 8 to 28 days from the date of the last visit's discharge date, the readmit code is '3'. Every visit to hospital has the field of CMG, doctor etc.

If the current visit has a readmit code of 2 or 3 I would like to know what the previous visit's CMG is so I can find out what kind of CMGs are potentially being discharged too early (causing them to be readmitted a short time later).

My comments about the time frame is that I was able to show the pre-CMG field as per my formula above based on grouping the chart number. All reports are run on discharge dates so my comment about not working for all is that if my timeframe for the report is Apr 1 to Jun 30, 2004 and a visit of Apr 7 has a readmit code of '3' (within 8 to 28 days of last visit), I can't show the "previous CMG" because the timeframe doesn't include March 10 to 30 which is what the previous discharge would have been.

So, below is an example of data and what I'd like to see:

chartnumber acct no readmit CMG Pre CMG
1234 3456 9 354 n/a
5678 6677 1 013 n/a
3456 8899 2 602 n/a
1234 9998 3 700 354
5678 2222 2 013 013

So what I'd like to see is a report grouped on the Pre-CMG field to know what CMGs may be a problem. Does that make sense?

I appreciate all assistance - thanks.

Shelby