Matching Records in Master Details Tables

[LHSCNaved]

Hello,

Environment: Crystal Report 2011 and Access 2003


I want to categorized patients who had a specific procedure or combination of procedures. For an example

Category 4 - Colonoscopy inspections with Excission have the following condition

At least one of:
CCI Procedure Code
1NK87BA
1NM59BAGX
1NM59BAHB
1NM87BA
2NK71BABJ
2NM71BABJ
2NK71BRBJ

and

At least one of:
CCI Procedure Code
1NA13BAFA
1NA50BABD
1NA50BABJ
1NA50BABP
1NA50CABJ
1NA87BA
1NF13BAGX

Whereas I have patients data in master details tables as following

CHARTNO OCCUR CODE
Patient A 1 1.NM.87.BA
Patient A 2 1.NQ.87.BA
Patient A 3 2.NM.70.BA-BJ
Patient B 1 2.NK.71.BA-BL
Patient B 2 2.NF.71.BA
Patient B 3 2.NA.71.BA
Patient B 4 2.NK.70.BA-BJ
Patient C 1 1.NT.87.LA
Patient C 2 1.NM.87.BA
Patient C 3 1.NQ.87.BA
Patient C 4 2.NM.70.BA-BJ

I am not sure how to match cases, as codes might be in different row and I can not use Next or Prev. And We have to match two criteria. Could somebody help me in this regard.

Thanks.

Naved Altaf

 

[fisheromacse]

Apologies in advance for any mistakes, typos or other errors in understanding and/or implementation.

I think you could group on Patient (ChartNo?)

create two formulas to evaluate if each criteria is met.

//{@True1}
numbervar tc1;
IF {Table.Code} in [1NK87BA, 1NM59BAGX, etc] then tc1 := tc1 + 1 else tc1 := tc1;
tc1

//{@True2}
numbervar tc2;
IF {Table.Code} in [1NA13BAFA, 1NA50BABD, etc] then tc2 := tc2 + 1 else tc2 := tc2;
tc2


Display the info per patient in the patient group footer and suppress the details.

then suppress the section (GF) in the section expert using something similar to: {@True1}>0 and {@True2}>0

 

[LHSCNaved]

Hi fisheromacse,

Thanks, I will apply the workaround and will get back.

Naved

 

[LHSCNaved]

Hi fisheromacse,

I Need to initialize the formula, as when it find the value then TC1 starts adding up.

For your kind feedback please.
Thanks

Naved

 

[Charliy]

Create the following formula
// @init
shared numbervar tc1 :=0
shared numbervar tc2 :=0


Create a Group Footer B for the group you're working with.
Put this formula in Group Footer B, and suppress Group Footer B
Also place this formula in the Report Header (suppress either the header or the formula)

 

[LHSCNaved]

Hi,

Thanks for the help it has started showing results. The other thing which is happening is the distinct count as it suppressing the group header so at the end number is overall. I just wanted to distinct count qualified cases.

Thanks.

 

[PeterDimitrov]

This query will return all patients with procedures in both lists:

SELECT * FROM [TableName] t
INNER JOIN (SELECT CHARTNO FROM [TableName] WHERE Replace(Code,'.','') in ('1NK87BA','1NM59BAGX','1NM59BAHB' ...)) l1 on t.CHARTNO =l1.CHARTNO
INNER JOIN (SELECT CHARTNO FROM [TableName] WHERE Replace(Code,'.','') in ('1NA13BAFA','1NA50BABD','1NA50BABJ'...)) l2 on t.CHARTNO =l2.CHARTNO

I am not sure that Replace(Code,'.','') is necessary - I am using it because the code in your table is in format 1.NM.87.BA while in the list it is 1NK87BA. You can avoid hardcoding procedure codes by creating tables with procedure identifiers but this is not part of the report.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[LHSCNaved]

Thanks Peter. This is also very helpful.