If then mulitple selection criteria

[teormond]

Thanks in advance for any response. I'm new to Crystal Reports and need some help. Using Crystal 10

I am writing a report to extract training records from our database with the following structure:
Table 1 Employee Table
ENumber
FirstName
LastName
etc

Table 2 Training Records
CourseNo
CourseDescription
StatusDate
etc

What I'm trying to do is have a row for each employee and a column for specific courses(CourseNo). If the employee has taken the course (indicated by a date in the Status_date field) I want a Y to appear. If the Status_date field is empty I want a N to appear.

I've created a formula for each course and inserted it into the detail along with enumber, firstname and lastname. It's returning Y on all records which I know is incorrect.

Here's my formula:

if IsNull({EMPTRAIN_MASTER.STATUS_DATE})
and ({EMPTRAIN_MASTER.COURSE_NO}='SL303')
then "N"
else "Y"

This is probably not the best way so any suggestions are welcome!

Thanks

 

[teormond]

Correction to previous post. I've grouped by employee and placed enumber, firstname, lastname and formula field into the group header so that the employee only appears once.

 

[lbass]

Make sure you have a left join from the Employee table to the Training table, and that you have no selection criteria on the training table. Then make your formulas like the following:

//{@SL303}:
if IsNull({EMPTRAIN_MASTER.STATUS_DATE}) or {EMPTRAIN_MASTER.COURSE_NO} <> 'SL303'
then "N"
else "Y"

//{@SL304}:
if IsNull({EMPTRAIN_MASTER.STATUS_DATE}) or {EMPTRAIN_MASTER.COURSE_NO} <> 'SL304'
then "N"
else "Y"

Place the formulas in the detail section and then right click on each and insert a summary. Use maximum as the summary. If they have taken the course, then "Y" will appear as the summary, otherwise "N" will. Drag the summaries into the group header.

-LB