Comparing Two Tables to find data that is in one but not in the other

[wesleylamb]

Hi,

I have been working with Crystal Reports for 6 weeks

I have three tables, Contact, Event and Course. Contact is a foreign key in Event and Course. Some Courses have events associated with them. I need to select an event and find out which students are not booked on a course that is associtated with that event. For instance Feb Diploma students(In Table:Course) who have not booked in on the July Weekend school(in Table:Event)

Thanks in advance

Wes

 

[Madawc]

For Crystal 8.5, you could do it as follows:

In Visual Linking Expert, create 'left outer' joins from Contact to both Event and Course. (I assume their is a single key. Left outer allows for Contracts who may or may not have a particular Event or a particular Course.)

In Record Select, do selections for a type of course and for a type of event. If you set these selections up as parameters, it will make it easier to do several tests without changing code.

Set Contacts as your detail line. Test isnull{event.keyfield} for each contact. Use the [Suppress] formula to suppress all Contacts except those with a combination you want reported.

When I write a new report, I generally check the validity of my tests by getting it to report everything, with some indicator of whether it passes the test. Then use suppress to get a neat report, once you are sure it works.

Madawc Williams
East Anglia
Great Britain

 

[wesleylamb]

I have tried the answer. If I left-outer-join course and event onto contract then include in the report as you say all contacts are shown and only events and courses where equal. But when I select on event and course, it shows just one record, that record of the person on a course who has also booked into the event. Thanks for your help, I will have to keep trying using the info that you have given me.

wes

 

[lbass]

If by "students" you mean {contacts}, and if you only want to look at students enrolled in {courses} then do an equal join from {contacts} to {courses} and a left join from {contacts} to {events}. Then you can write a formula {@bookstatus}:

if isnull({events.eventID} or {events.eventID} <> {?event} then 0 else 1

Group on {contacts.contactID} and then create the following formula {@bookedevent}:

if sum({@bookstatus},{contacts.contactID}) = 1 then &quot;Booked&quot; else &quot;Not Booked&quot;//place this in the group header with {Contacts.contactID} and student name, etc. For reference, place {?event} in the page header.

If you only want to display those who are not booked, then place the following formula in your group select statement:

sum({@bookstatus},{contacts.contactID} = 0

In terms of which courses are under consideration, you could create a parameter for that, too, or if you want to evaluate event enrollment for a set of courses, then group on courses, making {courses} Group 1, and {Contacts} Group 2.

-LB

 

[wesleylamb]

Thanks I really appreciate both replies, they have given me ideas for other reports. The second reply has worked really well, how can I thank you, yesterday I was way down now I am up there.

thanks

Wes