AnsweredAssumed Answered

Reporting problem when information is based on date calculation

Question asked by bnuttman on Jul 25, 2012
Latest reply on Jul 26, 2012 by opotoc

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?