Query/Report

[dbrooks]

I have 2 tables that I need to report on, one table has a bunch of student_id's which is my primary key, the second table has 2 fields one is a status, and one is a date. During the lifetime of the database it's going to have 2 status choices for each student, one is a "recieved", and one is a "completed" status, both attached to a certain date. What I have to report on is if a student has both a recieved and a completed status I need to get the date difference of those two fields. This seems like a pretty ordinary thing to do with a 1 to Many relationship. Can someone please help me out?????
Thank you so much! -db

 

[adz2013]

Open a query in design view and bring down both tables. If the Student_ID is in both tables then link them by student_ID. Then in the first field type
Rec:IIf(Status="recieved",Date)
In the second field type
Com:IIf(Status="Completed",Date)
Under the criteria for the first two fields type Is Not Null.
In the third field type
DateDiff("d",[Rec],[Com])
That will give you the difference in the dates. You might have to flip flop Rec and Com. Try this!