467,113 Members | 1,315 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,113 developers. It's quick & easy.

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

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
  • viewed: 2454
Share:
2 Replies
Rabbit
Expert Mod 8TB
You're probably looking at a subquery or DLookup(DMax()). Meta data would be helpful.
May 22 '07 #2
ADezii
Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 tblDROPOuts.Name, tblDROPOuts.Leave_Begin_Date, tblDROPOuts.Assignment_Begin_Date, tblDROPOuts.Program
  2. FROM tblDROPOuts
  3. WHERE tblDROPOuts.Assignment_Begin_Date<=[Leave_Begin_Date]
  4. ORDER BY tblDROPOuts.Assignment_Begin_Date DESC;
May 23 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Russell | last post: by
10 posts views Thread by Kenneth | last post: by
4 posts views Thread by Kissi5559 | last post: by
12 posts views Thread by Steve Elliott | last post: by
3 posts views Thread by MostlyH2O | last post: by
3 posts views Thread by www.ttdown.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.