469,963 Members | 1,924 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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 2618
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

Post your reply

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

Similar topics

1 post views Thread by jlm | last post: by
3 posts views Thread by Miller | last post: by
12 posts views Thread by jUrner | last post: by
25 posts views Thread by Umesh | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.