Group Formula to display selected values only

[mjm19]

This should be easy enough to do but escapes me.Using Crystal 8.0 with an ODBC / MSQL Database in a hospital setting. Doc wants a listing of those patients (group by patient ID) that had test A and test B done on the previous day. I can get ALL instances of A OR B, but only want to see those that have BOTH A and B.

Formula says:

{Query.EventType} = "OP" and
{Query.BatTstCode} in ["UA", "UC"]

And a Group Formula says:

COUNT ({Query.AccNumber}) >1

When I run the report I get the following:

Pt Number 123456

Accession # W1256 Test Code UA

Pt Number 123457

Accession # W1257 Test Code UA
Accession # W1258 Test Code UC

I only want to get the data in the second case where both the UA and UC were ordered on the same patient. Instead I'm getting all instances ...where UA and UC are ordered separately or together. The report should only get about 30 hits, yet I'm getting over 1000 because it's giving me ALL the UA's and UC's as described.
What can I write to only capture those instances as seen in
Patient # 123457 ?

Thanks,

MJM19

 

[lbass]

If patients can only have one test of each type per day then your formula should work if you make the following adjustment. First group on the patient ID and then go to report->edit selection formula->GROUP and enter:

COUNT ({Query.AccNumber},{table.PatientID}) >1

You were returning all records because you didn't specify that you meant the count within the patient ID, so it was using count for the report as a whole, and therefore all groups met that criterion.

-LB

 

[mjm19]

LB .... I did what you suggested in the group formula, but still get everything:

COUNT ({Query.AccNumber},{Query.PtNumber}) >1

Even if I change the 1 to any number, I still get every instance of either the UA or UC orders whether together (which is what I want) or by itself (which I don't want).

What do you think ??

 

[lbass]

You must have some duplicate records. Try:

distinctcount({Query.BatTstCode},{Query.PtNumber}) >1

Or, if the accession number is unique to each test-taking, then use:

distinctcount ({Query.AccNumber},{Query.PtNumber}) >1

Probably either one would work.

-LB

 

[mjm19]

Thanks ... It works now. Very much appreciate your help, as always.