developingprogrammer
asked on
how to check for over lapping time periods
hey guys,
i've got a table for how staff spend their time in the day. below is an example.
we can see that lunch overlaps with the 2nd day work.
when users, try and key in a time period, i want to ensure that there is no overlap. if there is an overlap, i want to raise a promp to disallow them from adding the new time period. a time period can start from when the last period ended e.g. 12pm - 2pm, 2pm to 6pm. doesn't need to be 2:01pm.
what's the best way to do this?
SQL / VBA? i can do it with a lot of code in vba (k haven't thought exactly how to do it but it would require loops and all), but i have a really, really, really big suspicion that SQL can do this quite easily with joins and criteria.
hrmm i'm really not very good with SQL right now and now is the time when all the SQL is coming together (yay!! haha), so could someone help me out with the SQL or VBA if it's not possible in SQL? thanks!! = ))
(if it's VBA then just general directions will do, i can probably figure it out myself, just a matter of how optimised it is in VBA or not)
StartTime EndTime Category
9:00 AM 12:00 PM Work
12:00 PM 2:00 PM Lunch
1:00 PM 6:00 PM Work
thanks guys!! = ))
i've got a table for how staff spend their time in the day. below is an example.
we can see that lunch overlaps with the 2nd day work.
when users, try and key in a time period, i want to ensure that there is no overlap. if there is an overlap, i want to raise a promp to disallow them from adding the new time period. a time period can start from when the last period ended e.g. 12pm - 2pm, 2pm to 6pm. doesn't need to be 2:01pm.
what's the best way to do this?
SQL / VBA? i can do it with a lot of code in vba (k haven't thought exactly how to do it but it would require loops and all), but i have a really, really, really big suspicion that SQL can do this quite easily with joins and criteria.
hrmm i'm really not very good with SQL right now and now is the time when all the SQL is coming together (yay!! haha), so could someone help me out with the SQL or VBA if it's not possible in SQL? thanks!! = ))
(if it's VBA then just general directions will do, i can probably figure it out myself, just a matter of how optimised it is in VBA or not)
StartTime EndTime Category
9:00 AM 12:00 PM Work
12:00 PM 2:00 PM Lunch
1:00 PM 6:00 PM Work
thanks guys!! = ))
ASKER
oh really? i didn't think of that! as in i didn't think the missing day post would solve this... err... wait... hrmm.. yea i guess if i use the start and end... hrmm ok ya i guess it would indirectly solve this. sorry Jeff i couldn't see so far ahead!! (far ahead for myself haha) = ))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
argh! complex stuff i need to load into my brain at 3 am in the morning!! haha
but really really good stuff Jim!!
ok let me try and digest it now = )
but really really good stuff Jim!!
ok let me try and digest it now = )
ASKER
hrmm ok since this is a check (BUT a super necessary check), i have to finish the module first before putting this "bell and whistle" on. well it isn't a bell and whistle. but i'm so short of time i gotta finish this thingy by tomorrow afternoon = (((
but super super good stuff Jim, i WILL fully digest and reply = ))
Jim this question is a lot more urgent - could you help me take a look? after reading the post you directed me to a little, i think that the answers for these 2 questions are different. one needs to generate records, one is a check = ) thanks Jim!!
https://www.experts-exchange.com/questions/28234613/which-day-in-a-date-range-doesn't-have-records.html?anchorAnswerId=39477346#a39477346
but super super good stuff Jim, i WILL fully digest and reply = ))
Jim this question is a lot more urgent - could you help me take a look? after reading the post you directed me to a little, i think that the answers for these 2 questions are different. one needs to generate records, one is a check = ) thanks Jim!!
https://www.experts-exchange.com/questions/28234613/which-day-in-a-date-range-doesn't-have-records.html?anchorAnswerId=39477346#a39477346
ASKER
hrmm Jim perhaps you can also share your VBA code for checking? i think i MIGHT need to go down the VBA path here. hrmm i'm not sure. but yup i definitely want to learn from your VBA code as well = )
thanks Jim!! = ))
thanks Jim!! = ))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
whao cool thanks Jim!!
2 questions:
1) why did you use do until BOF and moveprevious instead of do until EOF and movenext? cause later records would probably be overlapping first?
2) ah, i realised i didn't communicate what i wanted to do in a lucid enough fashion. i want to use a bound form to add the new record if it's not overlapping. if it's overlapping then the user will be prompted that they can't add it.
is there any way to do this with a bound form? haha i realise that's the crux of my whole endeavor - bound forms! i keep remembering what you told me last time to try and use Access as how it's meant to be used so that we can develop rapidly - which is what Access is meant to do, so now i'm trying to figure out how to work with it instead of overriding its functions = ) so bound forms is where i start!! = ))
2 questions:
1) why did you use do until BOF and moveprevious instead of do until EOF and movenext? cause later records would probably be overlapping first?
2) ah, i realised i didn't communicate what i wanted to do in a lucid enough fashion. i want to use a bound form to add the new record if it's not overlapping. if it's overlapping then the user will be prompted that they can't add it.
is there any way to do this with a bound form? haha i realise that's the crux of my whole endeavor - bound forms! i keep remembering what you told me last time to try and use Access as how it's meant to be used so that we can develop rapidly - which is what Access is meant to do, so now i'm trying to figure out how to work with it instead of overriding its functions = ) so bound forms is where i start!! = ))
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ah, great Jim! so sorry my poor communication skills again!! haha, i mean is there any way to do that with the SQL? (this may be a really potentially dumb question! so sorry if the answer is so obvious and i'm not seeing it!!)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Jim!! as always your solutions are fantastic!! this code and SQL is really superb!! thanks Jim!! = ))
Some of your questions themselves are "Overlapping".
Meaning the solution in one Q, may change the circumstances of another Q...
For example, you have not yet received an answer on your "missing days" post, so that may effect what you are asking here.
JeffCoachman