Closest date to a field from another field with multiple dates query

josecruz

I hope this make sense...
When students leave school (dropout, medical reasons, etc), there is a date field that admission use to record this event. For program evaluation, I want to know what program (Electronics, Nursing, Drafting, etc) they were in or assigned at the time when they went on leave. My problem is that some times students switch programs several times and I only want the closest assignment program to the Leave date range.
So for instance:
I have the fields Leave_Begin_date and Assignment_begin_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_date, the query should return 17 cases; however because some students switch departments I get 42 cases with some “duplicates and triplicates. Because I don’t care any assignments greater than the Leave_begin_date I use Assignment_begin_date<= to the leave_begin_date so this gets rid of anything higher than the leave date range 32 cases now. So here is where I need assistance

For instance:
John Doe, Assigned to Electronics 1/1/07 (don’t need it)
John Doe, Assigned to Drafting 1/15/07 (don’t need it)
John Doe, Assigned to Nursing 2/1/07 In this case I only want this
John Doe, Assigned to Manufacturing 2/20/07 (this will be eliminated with <= Leave date)
Please let me know if additional information is needed it such as sql or meta data.

Jose

May 18 '07 #1

Rabbit

You're probably looking at a subquery or DLookup(DMax()). Meta data would be helpful.

May 22 '07 #2

ADezii

I hope this make sense...
When students leave school (dropout, medical reasons, etc), there is a date field that admission use to record this event. For program evaluation, I want to know what program (Electronics, Nursing, Drafting, etc) they were in or assigned at the time when they went on leave. My problem is that some times students switch programs several times and I only want the closest assignment program to the Leave date range.
So for instance:
I have the fields Leave_Begin_date and Assignment_begin_date
I enter a date range between 2/5/07 and 2/15/07 for the leave_begin_date, the query should return 17 cases; however because some students switch departments I get 42 cases with some “duplicates and triplicates. Because I don’t care any assignments greater than the Leave_begin_date I use Assignment_begin_date<= to the leave_begin_date so this gets rid of anything higher than the leave date range 32 cases now. So here is where I need assistance

For instance:
John Doe, Assigned to Electronics 1/1/07 (don’t need it)
John Doe, Assigned to Drafting 1/15/07 (don’t need it)
John Doe, Assigned to Nursing 2/1/07 In this case I only want this
John Doe, Assigned to Manufacturing 2/20/07 (this will be eliminated with <= Leave date)
Please let me know if additional information is needed it such as sql or meta data.

Jose

Assuming your Table Name is tblDROPOuts, this SQL Statement will work but I'm sure there is a better solution. SQL is not my strong point.

May 23 '07 #3