cascading calculations in query need help

[jor]

Please help, i am new in access, and i am calculating time from 2 fields and
running time in total in the 24hr (hh:mm) format. I tried the suggestion of
Sharkbyte from Stephanie's query dated 1/23/2006 but i failed to do it.
Please see examples.

OnChk OffChk On/OffChkDiff Total Hours
8:58 13:35 4:46 1110:57 => carry forward figure.
8:50 12:57 4:07 1115:04
7:48 9:49 2:01 1117.05


1. I am calculating the difference from OnChk and OffChk and the result
will be added to Total hours. the difference of the 2nd row will be added in
the Total Hours from 1st row and difference from 3rd row will be added in the
2nd row.
2. I would like also that the grand Total Hours will be added in the form
and report.

Thanks in advance for your help.

jor

 

[Andy Hull]

Hi

I've had to make assumptions about your table structure but a query like the
following will give you a running total of hours.

Use the query as the source for your form or report.

select a.EmployeeID, a.WorkDate, a.OnChk, a.OffChk, format(a.Offchk -
a.Onchk, "hh:mm") as Diff,
(select format(sum(b.Offchk - b.Onchk),"hh:mm")
from tblHoursWorked b
where a.EmployeeID = b.EmployeeID
and (b.WorkDate + b.OnChk) <= (a.WorkDate + a.OnChk)
) as Total
from tblHoursWorked a
order by a.WorkDate, a.OnChk


Obviously, you will need to alter to suit your table / column names and will
join it to other tables to pick up things like EmployeeName if necessary.

I am guessing you will also want to restrict by date for a particular week,
month or year.

hth

Andy Hul

 

[Jamie Collins]

select a.EmployeeID, a.WorkDate, a.OnChk, a.OffChk, format(a.Offchk -
a.Onchk, "hh:mm") as Diff,
(select format(sum(b.Offchk - b.Onchk),"hh:mm")
from tblHoursWorked b
where a.EmployeeID = b.EmployeeID
and (b.WorkDate + b.OnChk) <= (a.WorkDate + a.OnChk)
) as Total
from tblHoursWorked a
order by a.WorkDate, a.OnChk

You seem to be using a column of type DATETIME (suitable for instants)
to store an interval. This effectively says that twenty-five-to-two-in-
the-afternoon minus two-minutes-to-nine-in-the-morning equals nearly-
twenty-to-five-in-the-morning.

Looks like the smallest time granule you are interested is one minute
so I suggest you use DATEDIFF to get the difference between the
DATETIME values in seconds and store the result in a column of type
INTEGER; you can then format as required in the display e.g. (for demo
purposes only):

SELECT TIMESERIAL(8, 58, 0) AS OnChk,
TIMESERIAL(13, 35, 0) AS OffChk,
DATEDIFF('S', OnChk, OffChk) AS interval_seconds,
FORMAT(TIMESERIAL(0, 0, interval_seconds), 'hh:nn') AS
interval_seconds_formatted

Jamie.

--

 

[azir662004]

You seem to be using a column of type DATETIME (suitable for instants)
to store an interval. This effectively says that twenty-five-to-two-in-
the-afternoon minus two-minutes-to-nine-in-the-morning equals nearly-
twenty-to-five-in-the-morning.

Looks like the smallest time granule you are interested is one minute
so I suggest you use DATEDIFF to get the difference between the
DATETIME values in seconds and store the result in a column of type
INTEGER; you can then format as required in the display e.g. (for demo
purposes only):

SELECT TIMESERIAL(8, 58, 0) AS OnChk,
TIMESERIAL(13, 35, 0) AS OffChk,
DATEDIFF('S', OnChk, OffChk) AS interval_seconds,
FORMAT(TIMESERIAL(0, 0, interval_seconds), 'hh:nn') AS
interval_seconds_formatted

Jamie.

--

Hi Jamie/Andy,

Thank you very much for the reply and solution. I tried it and i got a
good result. However, the runningTotalHrs was stopped in time 24:00
and start again at 1:00. So what i intend to do is to accumulate the
result as 24:00 + 15:00 = 39:00 and so on. .

As usual, Thank you for your assistance.

jor

 

[azir662004]

Hi

I've had to make assumptions about your table structure but a query like the
following will give you a running total of hours.

Use the query as the source for your form or report.

select a.EmployeeID, a.WorkDate, a.OnChk, a.OffChk, format(a.Offchk -
a.Onchk, "hh:mm") as Diff,
(select format(sum(b.Offchk - b.Onchk),"hh:mm")
from tblHoursWorked b
where a.EmployeeID = b.EmployeeID
and (b.WorkDate + b.OnChk) <= (a.WorkDate + a.OnChk)
) as Total
from tblHoursWorked a
order by a.WorkDate, a.OnChk

Obviously, you will need to alter to suit your table / column names and will
join it to other tables to pick up things like EmployeeName if necessary.

I am guessing you will also want to restrict by date for a particular week,
month or year.

hth

Andy Hul









- Show quoted text -

Hi Andy / Jamie,

Thank you very much for the reply and solution. I tried it and i got
a
good result. However, the runningTotalHrs was stopped in time 24:00
and start again at 1:00. So what i intend to do is to accumulate the
result as 24:00 + 15:00 = 39:00 and so on. .


As usual, Thank you for your assistance.

 

[Jamie Collins]

You seem to be using a column of type DATETIME (suitable for instants)
to store an interval [i.e. design flaw]

I suggest you use DATEDIFF to get the difference between the
DATETIME values in seconds and store the result in a column of type
INTEGER; you can then format as required in the display

Thank you very much for the reply and solution. I tried it and i got a
good result. However, the runningTotalHrs was stopped in time 24:00
and start again at 1:00. So what i intend to do is to accumulate the
result as 24:00 + 15:00 = 39:00 and so on. .

If you are count one second time granules as INTEGER as I suggested
then the value will not 'stop' at 24 or 24:00 but rather at
2,147,483,647 (approx 68 years).

Jamie.

--