5 Replies Latest reply on Jul 26, 2012 1:07 PM by opotoc

    Reporting problem when information is based on date calculation

    bnuttman

      I need some help getting started on this. Here is the situation:

      I have a students table and a movement table. One unique student per record in the students table and the same student can have many records in the movement table. The status table will have records with fields such as:

      student_id, date, status, reason, dorm, transferred_to;

       

      Examples are:

      student_id date status reason dorm transferred_to

      2051 1/15/2012 Entry Null Boone Null

      2051 5/31/2012 Transfer Null Boone Emery

      2051 7/22/2012 Discharge Graduate Emery Null

       

      2120 5/15/2012 Entry Null Boone Null

      2120 6/30/2012 Transfer Null Boone Mason

      2120 7/22/2012 Discharge Dropout Mason Null

       

      I need a report that displays the reason when a student is discharged and the dorm, (which is easy, the first is Graduate and the second is Dropout), but if the student has has Discharged within 30 days of transferring from another dorm, the report should show

      the dorm as being the dorm he transferred from, in the second example, it would be the Boone dorm, and the reason is Dropout.

       

      Basically, the report will list all student who where discharged, their reason and dorm, sorted by dorm and my only problem is the 30 day transfer and getting the correct dorm.

       

      Any ideas on this?