Table Logic

[TEM3]

Using Crystal Report 8.5 and Oracle tables:

In a report using two tables - LABCASE and ALL_ASSIGNMENTS left joined LABCASE.Case Key ---> ALL_ASSIGNMENTS.Case Key. There is only one unique Case Key in LABCASE. But since a "Case" can have multiple assignments to several different units, there can be several records in ALL_ASSIGNMENTS with the same Case Key field. I am trying to get a list of Cases (Case Number) from LABCASE that show an assignment to one unit (in ALL_ASSIGNMENT.Section) but not another (any case that had a DNA Unit Assignment, but no a Criminialistics Unit Assignment - most would have both so I am looking for exceptions.

 

[Madawc]

Group by LABCASE. Suppress the group header and details. In the group footer, check whether both types were found - you could do a pair of suitable running totals for the group, or maybe a 'maximum' and 'minimum' on a relavant field.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Madawc Williams (East Anglia, UK)

 

[lbass]

Group on {LabCase.CaseNumber} and then create a formula {@assignment}:

if {ALL_ASSIGNMENT.Section} = "DNA Unit Assignment" then 1 else
if {ALL_ASSIGNMENT.Section} = "Criminalistics Unit Assignment" then 10000 else 0

Then go to report->selection formula->GROUP and enter:

sum({@assignment},{LabCase.CaseNumber}) in 1 to 9999

This should return all groups with DNA, but not Criminalistics.

-LB