Calculating Time

[Adam1331]

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?

[puppydogbuddy]

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.....somethi ng like this (air code for illustrative purposes):

Select Table1.Employee,Table1.wDate, Table2.JobNo, Table2.TimeIn,Table2.Min(TimeIn) As MinStart, Table3.TimeOut,  Table3.Max(TimeOut) As MaxEnd 
From Table1, Table2, Table3
Where <(Table1.Employee = Table2.Employee  {your join fields} >
Group By Employee, JobNo, wDate, TimeIn, TimeOut
Having TimeIn = MinStart And TimeOut = MaxEnd
Order By Employee, JobNo, wDate 

Hope this helps.

[Adam1331]

Puppydogbuddy-

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?

[Adam1331]

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

[puppydogbuddy]

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:

http://bytes.com/forum/thread209027.html

[Adam1331]

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?

[Adam1331]

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.