Compare , Sort and Grouping

[LHSCNaved]

Hi All,

Before starting this thread I reviewed all previous post but could not find a clue, I am using CR XI and Access 2003.

My scenario is like this.

I have to track patients with their previous admissions within 90 days, and then count them for particular Wards.

In the first step I pull all the records without any criteria becuase I want to check their prevous admissions as they might admitted in any ward.

In the Second Step I wrrote a formula based on their visit number,admission date,disharge date and for specific wards I check their Prevous admissions. For that I sorted my report visit number and admission date.

Here comes the probem let suppose I have all the admission with ward numbers like 101 102 103 105 106 107 108 108 109 and I have to report only 102 104 105 108 109. If I sort my report on Ward numnber and visit number then I cannot evaluate them properly and If I sort them on Visit number then I cannot group them by spcecific Ward Numbers.

(There are more than 100 wards and I have to report around 30-40 wards. )

I need some directions/guideline to achieve this scenario.

I will appreciate your help in this regard.

 

[Madawc]

Can you remove the unwanted wards in your record selection? Something like

{ward} in [101, 102, 103, 105, 106, 107, 108, 108, 109]

If you need them all for some other purpose, you could suppress detail lines with unwanted wards.

not {ward} in [101, 102, 103, 105, 106, 107, 108, 108, 109]

Another alternative is grouping by wards and suppressing that. I'm not clear if your report could do that as well as its other functions.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Try using a command as your datasource. Go to new report->blank report->your datasource->add command and enter something like:

Select distinct table.`admissiondate`,table.`patientID`,table.`ward`
From table
where table.`admissiondate` in
(select distinct top 2 A.`admissiondate`
from table A
where A.`patientID` = table.`patientID`
Order by A.`admissiondate` desc)

This will return the last two dates per patient. Go to report->selection formula->GROUP and enter:

maximum({command.admissiondate},{command.patientID}) - minimum({command.admissiondate},{command.patientID}) < 91

Then insert a crosstab in the report header. Insert a formula {@patID} for the row:

if maximum({command.admissiondate},{command.patientID})-
minimum({command.admissiondate},{command.patientID}) < 91 then {command.patientID}

Use this formula {@ward} for the column field:
if {command.admissiondate} = maximum({command.admissiondate},{command.patientID}) then {command.ward}

Use this formula {@cnt} for your summary field (with a SUM inserted on it, not a count):
if {command.admissiondate} = maximum({command.admissiondate},{command.patientID}) then 1

-LB

 

[LHSCNaved]

Thanks all for your feedback, I will try the workflow defined by LBASS, which I hope will be on target.

Thanks a lot.