problem with my formula to create a group by field 1

[krystalball]

Hi, I am a total beginner at crystal reports. Here is my problem.

I have two tables 1) Accidents 2) Injuries

I need to create a formula for grouping purpose. The report will end up with three groups so each accident will fall into one of these 3 groups.

1) Accident without an injury (This is where a record exists in the accidents table but not in the injuires table)

2) Accident with an injury (This is where a record exists in the accidents table and has a record in the injuires table as well)

3) Incident (where the accident_type in the accident table is "incident" and does not have a corresponding injuries record". If a corresponding record is found in the injuries table, then this must be grouped with the "accident with injury")

Once, I have created the formula, I can then group by it and do my totals etc.

Any help would be really appreciated. Thanks

 

[mbarron]

The following method should work.

Use a left outer join from Accidents to Injuries.


if isnull({injury_type}) and {accident_type} = "Accident"} then "Accident w/o injury" else
if if isnull({injury_type}) and {accident_type} = "Incident"} then "Incident else
"Accident with Injury"

Mike

 

[kskid]

Make sure your link from the Acccident to the Injury table is left outer joined.

 

[mbarron]

Yeah, that's what I said.

Use a left outer join from Accidents to Injuries

Mike

 

[krystalball]

Thanks guys, All working now and users are happy.