Thank you for this site!

 

I have a Crystal Report I inherited that I am revising.  It is using tables and not  Add a Command sql statements.

 

I need to access another table.  On this table, students have multiple records on a table, and I need to query the residency code in the correct record of the multiple records for each student.

 

For example:

 

Student ID    Effective Term Code   Residency Code

 

1111               201430                           X

1111               201320                           P

1111               201310                           X

1111               201420                           X

 

For student 1111 I need the record that has the maximum term<={?Term Code}.

 

The {?Term Code} is 201410.  Therefore, the correct record to pick up for this student is the 201320 term code record, because it is the maximum term code record <= 201410.

 

I can do this easily in sql, but can't seem to incorporate it into this Crystal Report. 

 

I've tried using Maxiumum in group selection formulas, record selection formulas, etc..., and nothing works.  I could have easily added it to a Command selection, if the report had been created using that as a selection source instead of linked tables.

 

There is alot in this report, so converting it to using a Command as selection source has been problematic, and I hate to start from scratch unless I absolutely have to.

 

Thanks for anyone's assistance!

 

 

Thanks!  I had tried the Add Command before but I was using only a SELECT statement.

 

After your advice I went back and tried a few more things. 

 

What I needed was to do an INNER JOIN, then the SELECT.  That worked.

 

My code in the Command was:

 

inner join
 (
    select s2.ID,
        max(s2.Term_Code) as max_term
        from student_table 2
        where s2.Term_Code <= '{?Term}'
        group by s2.ID
        ) s3
 on student_table = s3.ID                                                 
 and student_table.Term_Code = s3.max_term
 and student_table.Resd_Code = 'X'

 

The student_table above with no alias is joined to the other tables in the Crystal Report.   

 

Thanks again!!!  You were very helpful!