Question by trater04: How to calculate the difference of the same field in two rows in Microsoft Access?
I have a table that has clock in time in one row and clock out time in another row. I need to be able to sum the hours worked in a day.
TIMESTAMP ETEMP# ETIORO PMNAME
1/2/09 8:56 AM 213 I John Deere
1/2/09 1:15 PM 213 O John Deere
1/2/09 1:43 PM 213 I John Deere
1/2/09 6:03 PM 213 O John Deere
I created two queries in access, one with clock in data and one with cloc out data and added the field DateVal for the day. I then created a query with the following SQL language.
SELECT ClockIn.DateVal, ClockIn.[ETEMP#], ClockIn.PMNAME, ClockIn.ETIORO, ClockOut.ETIORO, ClockIn.TIMESTAMP, ClockOut.TIMESTAMP, DateDiff("n",[clockin].[timestamp],[clockout].[timestamp])/60 AS [Hours]
FROM ClockIn INNER JOIN ClockOut ON (ClockIn.[ETEMP#]=ClockOut.[ETEMP#]) AND (ClockIn.DateVal=ClockOut.DateVal)
WHERE (((ClockIn.DateVal)=#1/2/2009#) And ((ClockIn.PMNAME)="John Deere"));
This is the result I get:
DateVal ETEMP# PMNAME ClockIn.ETIORO ClockOut.ETIORO ClockIn.TIMESTAMP ClockOut.TIMESTAMP Hours
1/2/09 213 John Deere I O 1/2/09 1:43 PM 1/2/09 1:15 PM -0.466666667
1/2/09 213 John Deere I O 1/2/09 8:56 AM 1/2/09 1:15 PM 4.316666667
1/2/09 213 John Deere I O 1/2/09 1:43 PM 1/2/09 6:03 PM 4.333333333
1/2/09 213 John Deere I O 1/2/09 8:56 AM 1/2/09 6:03 PM 9.116666667
How do I eliminate the duplication and get true hours worked for the day. I think it can be done with a subquery, but that is out of my league. Any help is greatly appreciated.
Best answer:
Answer by Betty W
I don't know access syntex, but I can give you my 2 cents how to achive this using one simple query using aggregation.
convert your timestamp to minutes (timestamp - the date), then 8:56 will be 8x60 + 56 = 536, 1:15 => 795
1:43 =>823, 6:03=>1083. If Access can handle case statement, that will be great, if not, add a column called "sign", -1 for clock in, 1 for clock out.
select Sum (theCovertedMinutes * sign)
from your table
group by PMName
You will get -536+795-823+1083 = 519 minute
Add your own answer in the comments!
Technorati Tags: Access, calculate, difference, field, Microsoft, rows, same