Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare , Sort and Grouping

Status
Not open for further replies.

LHSCNaved

MIS
Dec 20, 2006
20
CA
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.


 
Can you remove the unwanted wards in your record selection? Something like
Code:
{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.
Code:
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.


[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
Thanks all for your feedback, I will try the workflow defined by LBASS, which I hope will be on target.

Thanks a lot.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top