Sum up the values calculated.

[janue]

hi I am making a late report of each employee's data and I calculated for each individual day by having late for how long, but I would like to make another column which displays the total amount of time late. This is currently what I have now:

My SQl looks like this:

SELECT username, DateValue(clocktime) AS Date_Clocked, Min(TimeValue(clocktime)) AS Clock_In_Time, ((Clock_In_Time-#9:00:00 AM#)*24) AS Late_By_inHours
FROM tablename
GROUP BY username, DateValue(clocktime)
HAVING Min(TimeValue(clocktime))>#9:00:00#;

clocktime is a date/time field, in reality it is regardless whether it is clock in or clock out time. And now I want my table to look smth like this:

Has anyone knows how to do smth like this? I tried using the SUM() but it states error stating it cant take the expression ((Clock_In_Time-#9:00:00 AM#)*24) smth lidat

 

[Ron_dK]

You might try a totals query.
The sql would be something like :

SELECT Yourtable.username, Sum(Yourtable.late_by_inhours) AS tothours
FROM Yourtable
GROUP BY Yourtable.username;

Hth

 

[janue]

it's ok i did it