By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

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

P: 13
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
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,347
You're probably looking at a subquery or DLookup(DMax()). Meta data would be helpful.
May 22 '07 #2

ADezii
Expert 5K+
P: 8,619
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.