472,353 Members | 1,587 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Calculating Time

I am currently setting up a database for work that will operate as a timesheet. I have set up multiple tables, queries, forms and reports to help this process run smoothly.

The issue that has come up deals with calculating our times. An employee would use one form based on one table to punch into a job and the time is recorded using the value [=Now()] and then will punch out of a job on a seperate form based on a seperate table which also uses the [=Now()] function.

I linked these two tables using Job #'s, Process #'s, and Employee Names. Using these three identifiers, there would be no repeats and would uniquely link the two times. This was working great until these three fields did have a repeat (say if someone punched out on a job for lunch and then back into the same job and process after lunch).

When this happens, the query that links the start times and the end times will link both start times with both end times giving me four records instead of two. (Ex: 11:30-12:00, 12:30-1:00, 11:30-1:00, 12:30-12:00)

Any suggestions?
Jul 18 '08 #1
6 2786
1,923 Expert 1GB
Try using Min and Max functions in your query to give you the earliest start time and latest end time for that employee and job # for any given day.....something like this (air code for illustrative purposes):

Expand|Select|Wrap|Line Numbers
  1. Select Table1.Employee,Table1.wDate, Table2.JobNo, Table2.TimeIn,Table2.Min(TimeIn) As MinStart, Table3.TimeOut,  Table3.Max(TimeOut) As MaxEnd 
  2. From Table1, Table2, Table3
  3. Where <(Table1.Employee = Table2.Employee  {your join fields} >
  4. Group By Employee, JobNo, wDate, TimeIn, TimeOut
  5. Having TimeIn = MinStart And TimeOut = MaxEnd
  6. Order By Employee, JobNo, wDate 
Hope this helps.
Jul 19 '08 #2

Using the MinStart time and MaxEnd time function works great but the problem is that it doesn't account for the time the employee was on break. So now it will calculate just one time, 11:30-1:00 and omit the 12:00 clock out time and the 12:30 clock in time. Now it comes up with a total time of 1.5 hours on the job instead of 1 hour.

I also played with the Sum function a little seeing that the Sum of the [Start Time] subtracted from the Sum of the [End Time] will give the correct time on the project with some manipulation. The issue I have with this is that because it links both start times with both end times I recieve four records instead of two and the sum function will end up to be twice what it should (Ex: 11:30 + 12:30 = 48 instead of the 24 it should be). Is there a way to place a No Duplicates rule on just the [Start Time] and [End Time] section of the query?
Jul 22 '08 #3
And actually if I could input a No Duplicates rule I could just sort the times as ascending and that would solve the issue as well.

Thanks again for your help
Jul 22 '08 #4
1,923 Expert 1GB
And actually if I could input a No Duplicates rule I could just sort the times as ascending and that would solve the issue as well.

Thanks again for your help
Try using Select DistinctRow or Select Distinct in your query as discussed in this thread:

Jul 22 '08 #5
This doesn't seem to work for my database or my lack of experience prevents me from being able to make it work. I do have another direction I could take things if you could help me with a different problem.

Originally there were two forms. One was a [Check In Form] which recorded the information to the [In Table]. The other was a [Check Out Form] based on a query that was run on the [In Table]. The problem with this was that I couldn't find a way to record the [=Now()] function into a record pulled by a query. Is there a way to do this, maybe with a command button or action?
Jul 22 '08 #6
Thanks again for your help. I think I have it figured out. After my last post I created a macro (which I haven't done before) using the SetValue and linked it to a command button on a form that pulls the records from a query. This way I can use just one table and still use the [Now()] function. If you can forsee any problems with this let me know, otherwise thanks again for your time and help.
Jul 22 '08 #7

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

Similar topics

by: Ron Adam | last post by:
Hi, I'm having fun learning Python and want to say thanks to everyone here for a great programming language. Below is my first Python program...
by: Kasp | last post by:
Hi there, I am trying to make an OLAP cube on a table having two columns (datetime, Number_of_times_an_event_occured). My dimension is time and...
by: jlm | last post by:
I have a form which feeds table (TblEmpLeave) of Employee Leave Time (time taken off for Administrative, Annual, Sick, Compensation leave). I have...
by: Miller | last post by:
Hi, Can someone tell me how to calculate MFLOPS for the following C# code (on a Pentium 4 2.0 Ghz)? for (i=0; i<n; i++) { for (j=0; j<n;...
by: jUrner | last post by:
Hello all I have the problem of how to calculate the resolution of the system clock. Its now two days of head sratching and still there is...
by: Umesh | last post by:
i want to calculate the time required to execute a program. Also i want to calcute the time remaining for the execution of the program. how can i...
by: Blaize | last post by:
Hi, I'm having an issue trying to calculate time. Its okay if the value does not exceed 24 hours otherwise I get a date and hours listed. For...
by: freddieMaize | last post by:
Hi there, I need to hit few list of sites from my application and find the response time of those site and sort them (which ever is opening first...
by: mfaisalwarraich | last post by:
Hi everybody. I have some problem while calculating time. i have employees who are working on hourly basis. i want to calculate the time. for example...
by: Missionary2008 | last post by:
Thank you in advance for your help. I'm trying to calculate the Total Time in hours and minutes to complete a job. The way we are calculating it...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.