Refer to previous record based on results of current

[shelby55]

Hello

I am using CR9 on a Windows XP/SP2 computer accessing an Access XP database via ODBC DSN.

I'm not sure that it is possible to do what I require but here goes:

I have a patient database where chartno is the patient identifier (number used for patient for every visit), acct no is the visit identifier (database unique number - no duplicates), readmit as a code indicating status of visit. Other data elements include CMG, MRP and MrDx.

If a patient comes in within 28 days of a previous visit with same/related diagnosis the readmit code is 2 or 3. So if the current visit has readmit code 2 or 3 I want to display the previous visit CMG and MRP information.

Hoping someone can help - thanks.

Shelby

 

[lbass]

Use formulas like:

//{@CMG} to replace {table.CMG}:
if {table.code} in [2,3] then previous({table.CMG}) else {table.CMG}

//{@MRP} to replace {table.MRP}:
if {{table.code} in [2,3] then previous({table.MRP}) else {table.MRP}

-LB

 

[shelby55]

Hi LB

I know I shouldn't doubt you but shouldn't the formula be linked to chart number?

Sample database:
chartno acct no readmit MRP CMG
1234 55555 1 030 013
2456 66666 9 020 253
7890 44444 1 020 222
1234 77777 3 040 851

So in this example, for Chart No 1234, I want to show CMG 013. The other two charts don't apply because they don't have readmit code 2 or 3 and therefore no previous visit.

Shelby

 

[synapsevampire]

I think that LB assumed that you were grouping by the chart number and the code 2 or 3 meant that the previous row would always be the one you wanted.

Adding to his thought you'd need to do a few additional steps.

Group by Chart
Order by date
Place the fields in the group footer

You might also adjust the formula a bit to:
//{@CMG} to replace {table.CMG}:
if {table.code} in [2,3]
and
previous({table.chartno}) = {table.chartno} then
previous({table.CMG})
else
{table.CMG}

-k

 

[lbass]

Yes, I did assume you were grouping on chart, and that the records were at least sorted by readmit code. I also assumed that there were only codes 2 and 3 if there was a code 1, which means that you wouldn't really have to add the check that SV added ({table.chartno} = previous({table.chartno})), although it doesn't hurt to have that there.

I don't necessarily think there's a need to place the fields and formulas in the group footer--unless you only want to show one record per chart. I thought you only wanted to show previous fields for certain fields in the records where code = 2 or 3, but still show the rest of the record as is.

-LB