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;
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?