Left Outer Join???

[jcfowl]

We have a Database where events are Scheduled with Projected
data and Recorded with Actual Data. The Tables for
Scheduling are the same as the "Actual" Tables.
The Diagram Below is the Setup for our Tables with the
Project Table as the Link. All Tables have a one to many
relationship with the project.

Schedule1-----------v v-----------Actual1
Schedule2-------->Project<--------Actual2
Schedule3-----------^ ^-----------Actual3

I want to report What Items have been Scheduled and not
been Recorded in the Actual Tables. Basically a To-Do list.
The problem is, I am using a Left outer join but my results
get doubled if more than one item is scheduled for any of
the 6 tables. I grouped my information by Project Key and
am using the Count of the Schedule to compare with the
Count of the Actual. If Count(Schedule1.pkSchedule1) >
Count(Actual1.pkActual1) Then The task is not complete.
I can't seem to avoid the cartesian product though. Any advice would be appreciated.

 

[chelseatech]

You will need to join the actual table to the schedule table and report off the UNION of the two tables.

A SQL UNION join will do this - you will need to do it in the DB as CR can't do it by itself. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Ngolem]

A way to do a &quot;union&quot; in a crystal report is to use subreports.

Make the main report based one relationship and subreports for the other two relationships linked to the main report by a common field.

This isolates and prevents the problems that you are describing without having to use any SQL Union....although if you can do the latter it will make your report much simpler...but sometimes you cannot JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.