Missing Data Report

[ldalessio]

Hi Everyone.
I am soooo baffled. I am using Crystal Reports XI and trying to create a report of missing teacher attendance for a specific day. I have two tables; (1) containing the course data and (2) containing if the teacher DID take attendance by date and course. Example of table data:
Table 1:
ART 01 TEACHER 222
ART 02 TEACHER 222
ART 03 TEACHER 222
Table 2:
ART 01 TEACHER 222 DATE 6/1/09
ART 03 TEACHER 222 DATE 6/1/09
ART 01 TEACHER 222 DATE 6/2/09
ART 02 TEACHER 222 DATE 6/2/09
ART 03 TEACHER 222 DATE 6/2/09
If I select date 6/1/09 in my parameters, I should see a report containing;
ART 02 TEACHER 222

I have tried making Table 1 my primary with every link option and I have tried making Table 2 my primary with every type of link option. No luck.

Any suggestions????
Thank you for all your help.

 

[CoSpringsGuy]

Just from what Im reading, if you select the date 6/1/09 in your parameter in your record selection, Im not sure how you would expect to get ART 02 TEACHER 222 as a result since there was no entry for that date.

Maybe you could do a left outer join from Table 1 and place your parameter field in a formula that looks for either the parameter date or null value in your date field.

if {datefield} = {?dateparameter} then totext({datefield}
else if isnull({datefield} then "Not in attendance"

group by teacher

create a summary (maximum) for that formula and place it in the group footer. hide the details.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

You could try using a left join FROM Table1 TO Table 2 on Teacher ID and class with NO selection criteria on Table 2. Then suppress the detail section with a formula like this:

not isnull({Table2.teacherID}) or
{Table2.date} <> {?Date}

It would be better to use a command like this as your datasource:

select table1.teacher, table1.class
from table1 left outer join table2 on
table1.teacher = table2.teacher and
table1.class = table2.class and
table2.date = {?Date}

Note that no "where" clause is used. By using "and" in the from clause, the left outer join is preserved.

-LB