Solved

Microsoft Access 2010 - Timesheet database Catch overlap time entries except for

Posted on 2013-11-29
6
715 Views
Last Modified: 2013-11-29
I have a database that employees enter their time on a certain project and it will warn them if they submit the time and it overlaps the time on another project. For example I enter 7/29/2013 7:00 AM start time and enter 7/29/2013 8:00 AM stop time. Then on another project I enter 7/29/2013 7:50 AM start time and enter 7/29/2013 9:00 AM it is a conflict. (7:50 to 8:00) Works great no issues. Now they want to be able to enter start time 7/29/2013 8:00 AM and stop time 7/29/2013 9:00 AM and this not being a conflict.  What is the best way to handle this.
Here is the current query.
SELECT tbltimesheet.EventID, tblEvent_1.EmpName, tbltimesheet.LocationID, tbltimesheet.Starttime, tbltimesheet.Stoptime, tbltimesheet.Sel, tbltimesheet.systemnotesid
FROM tbltimesheet INNER JOIN tbltimesheet AS tblEvent_1 ON tbltimesheet.EmpName = tblEvent_1.EmpName
WHERE (((tblEvent_1.EmpName)=networkusername()) AND ((([tblEvent_1].[Starttime]>=[tbltimesheet].[StopTIme]) Or ([tblEvent_1].[StopTime]<=[tbltimesheet].[Starttime]) Or ([tbltimesheet].[EventID]=[tblEvent_1].[EventID]))=0))
ORDER BY tbltimesheet.Starttime;
Thanks for all the help.
0
Comment
Question by:marlind605
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39685349
check if the method used here,
 http://support.microsoft.com/?kbid=245074 is applicable to your  problem..
0
 

Author Comment

by:marlind605
ID: 39685372
capricorn1 from the website I see "By subtracting 1 day from the reservation checkout date, you can book a date that is the last day of an existing reservation. " How would I change the query to allow for the same time?
0
 
LVL 57
ID: 39685430
Please read the following:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28176550.html#a39301783

It's exactly what you need.

Jim.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 57
ID: 39685435
This comment is probably the most consise in expaling the logic required:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28176550.html#a39301962

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39685440
and just to be clear, the check is:

WHERE (existing start date) <= New END Date AND (existing end date) >= New START date


 New END / START would be the time sheet your just entering.

Jim.
0
 

Author Closing Comment

by:marlind605
ID: 39685735
Jim, that was awesome. Thank you.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question