This is going to be really rough and won't work if someone works over
midnight. It also depends on the Date and Time fields being date/time
datatypes. It only works with In and Out. If there is some other description,
all bets are off.
First create two queries like so with the proper table and field names.
qryTotalTimeIn:
SELECT TotalTime.EmployeeNumber,
TotalTime.Date, TotalTime.Description,
Sum(TotalTime.Time) AS SumOfTime
FROM TotalTime
GROUP BY TotalTime.EmployeeNumber,
TotalTime.Date,
TotalTime.Description
HAVING (((TotalTime.Description)="IN")) ;
qryTotalTimeOut:
SELECT TotalTime.EmployeeNumber,
TotalTime.Date,
TotalTime.Description,
Sum(TotalTime.Time) AS SumOfTime
FROM TotalTime
GROUP BY TotalTime.EmployeeNumber,
TotalTime.Date,
TotalTime.Description
HAVING (((TotalTime.Description)="OUT")) ;
Finally join the two and get the answer:
SELECT qryTotalTimeIn.EmployeeNumber,
qryTotalTimeIn.Date,
CDate([qryTotalTimeOut].[SumOfTime]-[qryTotalTimeIn].[SumOfTime]) AS TheHours
FROM qryTotalTimeIn INNER JOIN qryTotalTimeOut ON (qryTotalTimeIn.Date =
qryTotalTimeOut.Date)
AND (qryTotalTimeIn.EmployeeNumber = qryTotalTimeOut.EmployeeNumber);
Note: Date, Time, and In are all reserved words and some strange thing could
happen using these field names. If you aren't too far along in the design,
you might want to change them to something like HoursDate, HoursHours, TimeIn.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Marco said:
Hello. I need to calculate the diference between diferente hours, but those
hours are are in diferente recordsets.
Like this:
-----------------------------------------------------------------------------
EmployeeNumber Date Time Discription
0001 15-05-07 08:00 In
0001 15-05-07 13:00 Out
0001 15-05-07 14:00 In
0001 15-05-07 17:00 Out
How do I calculate this? It should return the result of 08:00 hours worked.
Please help me!!!
Marco