Diference between hours when hours in diferent recordset

[Guest]

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

 

[RHutcheson via AccessMonster.com]

Create a function and paste in the following

'Perform Starting Minutes Calculation (Calculate from the previous midnight)
lngStartMinutes = (Hour(strStartTime) * 60) + (Minute(strStartTime))

'Perform Ending Minutes calculation (Calculate From The Previous Midnight)
lngEndMinutes = (Hour(strEndTime) * 60) + (Minute(strEndTime))

'Subtract Start Minutes From End Minutes To Isolate The Time Span as Hours
and Minutes
dblTimeSpan = CDbl(Format(((lngEndMinutes - lngStartMinutes) / 60), "#.00"))

You will need to create a routine to extract the start time and stop time for
your employee and to pass that to the function along with the employee number
.. Tell your function to return the hours and minutes as a decimal value
(data type DOUBLE).

The code figures minutes from midnight and then subtracts starting value from
ending value which gives you the time interval in between. If you have time
spanning midnight, a few extra steps are required to get the right answer. I
created a "Time-Tracker" for one of my clients using this process. It allows
them to see how much time is spent on any process they want to evaulate over
any given period of days.

RHutcheson

 

[Guest]

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.

 

[Guest]

Hi. Thanks for your time. But i'm so lost there.

OK, I put that code in function. But I do I call the funciton?

Should I call that for the report? based on que the day and employee number?

Thanks once again,
Marco

 

[John Spencer]

Tricky part here is making sure your data is good. That is for every out
there is an in that corresponds to the correct date and time.

You could T R Y this untested query.

SELECT EmployeeNumber, [Date],
Sum( DateDiff("n",(SELECT Max([Date] + [Time])
FROM YourTable as T
WHERE T.EmployeeNumber = YourTable.EmployeeNumber
AND T.Description = "IN"
AND T.Date + T.Time <= YourTable.Date + YourTable.Time) ,[Date]+[Time]))
as ElapsedMinutes
FROM YourTable
WHERE Description = "Out"
GROUP BY EmployeeNumber, [Date]
ORDER BY EmployeeNumber



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I Jerry. thanks for your time. I try that in Excell and it worked. Do you
think it will work for ever?

It worked on this example:
-----------------------------------------------------------------------------
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

Thanks,
Marco

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.

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

 

[Guest]

Hi. It seems you are my guides in Access. How lonn you work in Access?

About this topic, is any way to tell the program that the diference is
calculated with something like this: (this would be probably on the report)

If total records count = 2 then hour_ge (on the record2) - hour_ge (of the
record1) else
if records count = 4 then (hour_ge (on the record2) - hour_ge (of the
record1))+(hour_ge (on the record4) - hour_ge (of the record3))
end if

Is this possible?

Regards,
Marco

Tricky part here is making sure your data is good. That is for every out
there is an in that corresponds to the correct date and time.

You could T R Y this untested query.

SELECT EmployeeNumber, [Date],
Sum( DateDiff("n",(SELECT Max([Date] + [Time])
FROM YourTable as T
WHERE T.EmployeeNumber = YourTable.EmployeeNumber
AND T.Description = "IN"
AND T.Date + T.Time <= YourTable.Date + YourTable.Time) ,[Date]+[Time]))
as ElapsedMinutes
FROM YourTable
WHERE Description = "Out"
GROUP BY EmployeeNumber, [Date]
ORDER BY EmployeeNumber



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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

 

[John Spencer]

I've been working with databases for over 20 years. I've been working with
Access for 9 years.

I don't know of a way to do what you are asking. The query I posted should
give you a sum of the time expended by date by employee. One problem is
that if the In and Out times are on different dates then you may get
erroneous durations of time. You can manipulate that sum of minutes -
ElapsedMinutes - in the report using the following as control source to get
hours and minutes.

=Int([ElapsedMinutes]/60) & ":" & Format([ElapsedMinutes] Mod 60,"00")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. It seems you are my guides in Access. How lonn you work in Access?

About this topic, is any way to tell the program that the diference is
calculated with something like this: (this would be probably on the
report)

If total records count = 2 then hour_ge (on the record2) - hour_ge (of the
record1) else
if records count = 4 then (hour_ge (on the record2) - hour_ge (of the
record1))+(hour_ge (on the record4) - hour_ge (of the record3))
end if

Is this possible?

Regards,
Marco

Tricky part here is making sure your data is good. That is for every out
there is an in that corresponds to the correct date and time.

You could T R Y this untested query.

SELECT EmployeeNumber, [Date],
Sum( DateDiff("n",(SELECT Max([Date] + [Time])
FROM YourTable as T
WHERE T.EmployeeNumber = YourTable.EmployeeNumber
AND T.Description = "IN"
AND T.Date + T.Time <= YourTable.Date + YourTable.Time)
,[Date]+[Time]))
as ElapsedMinutes
FROM YourTable
WHERE Description = "Out"
GROUP BY EmployeeNumber, [Date]
ORDER BY EmployeeNumber



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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

 

[Guest]

Hi. let it be.

Well, I work intensive with Access not even for a year. I did a lot of small
things but nothing special. But in my opion Access is very good. Ok, may not
be the best type of database on the market, it it's very good.

Now I have this problem to solve, and may database has more then 1.200.000
record and it's working fine. the size is 286mb and no problem.

I'm from portugal.


I check that even with those erroneous records the the calc values are the
same.

Can you check what wrong with this DLOOKUp that I want o insert on a report?

=DLookUp("[Hora_Ok]";"[7_cons_Entradas_Saidas_Renwin_Saldo]";"[Employeenumber] = '" & [Employeenumber] & "' and [DataMarcacao]= '" & [DataMarcacao] & "'")

I have a report base on a querie. and I need to lookup for a value in
another querie.

There fiels that I want to retrieve is Hora_Ok and is on querie named:
7_cons_Entradas_Saidas_Renwin_Saldo

The criteria are: DataMarcacao must be equal of datamarcacao of the report
and employeenumber equal of EmployeeNumber of the report.

What's wrong with my DLOOKUP?

Best Regards,
Marco

I've been working with databases for over 20 years. I've been working with
Access for 9 years.

I don't know of a way to do what you are asking. The query I posted should
give you a sum of the time expended by date by employee. One problem is
that if the In and Out times are on different dates then you may get
erroneous durations of time. You can manipulate that sum of minutes -
ElapsedMinutes - in the report using the following as control source to get
hours and minutes.

=Int([ElapsedMinutes]/60) & ":" & Format([ElapsedMinutes] Mod 60,"00")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. It seems you are my guides in Access. How lonn you work in Access?

About this topic, is any way to tell the program that the diference is
calculated with something like this: (this would be probably on the
report)

If total records count = 2 then hour_ge (on the record2) - hour_ge (of the
record1) else
if records count = 4 then (hour_ge (on the record2) - hour_ge (of the
record1))+(hour_ge (on the record4) - hour_ge (of the record3))
end if

Is this possible?

Regards,
Marco

Tricky part here is making sure your data is good. That is for every out
there is an in that corresponds to the correct date and time.

You could T R Y this untested query.

SELECT EmployeeNumber, [Date],
Sum( DateDiff("n",(SELECT Max([Date] + [Time])
FROM YourTable as T
WHERE T.EmployeeNumber = YourTable.EmployeeNumber
AND T.Description = "IN"
AND T.Date + T.Time <= YourTable.Date + YourTable.Time)
,[Date]+[Time]))
as ElapsedMinutes
FROM YourTable
WHERE Description = "Out"
GROUP BY EmployeeNumber, [Date]
ORDER BY EmployeeNumber



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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

 

[John Spencer]

You need to delimit dates with # sign and make sure they are in US format
dd/mm/yyyy or in the format yyyy/mm/dd.

Try

=DLookUp("[Hora_Ok]";"[7_cons_Entradas_Saidas_Renwin_Saldo]";"[Employeenumber]
= '" & [Employeenumber] & "' and [DataMarcacao]= #" &
Format([DataMarcacao],"yyyy-mm-dd") & "#")


See Allen Browne's article
International Dates in Access at:
http://allenbrowne.com/ser-36.html

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. let it be.

Well, I work intensive with Access not even for a year. I did a lot of
small
things but nothing special. But in my opion Access is very good. Ok, may
not
be the best type of database on the market, it it's very good.

Now I have this problem to solve, and may database has more then 1.200.000
record and it's working fine. the size is 286mb and no problem.

I'm from portugal.


I check that even with those erroneous records the the calc values are the
same.

Can you check what wrong with this DLOOKUp that I want o insert on a
report?

=DLookUp("[Hora_Ok]";"[7_cons_Entradas_Saidas_Renwin_Saldo]";"[Employeenumber]
= '" & [Employeenumber] & "' and [DataMarcacao]= '" & [DataMarcacao] &
"'")

I have a report base on a querie. and I need to lookup for a value in
another querie.

There fiels that I want to retrieve is Hora_Ok and is on querie named:
7_cons_Entradas_Saidas_Renwin_Saldo

The criteria are: DataMarcacao must be equal of datamarcacao of the report
and employeenumber equal of EmployeeNumber of the report.

What's wrong with my DLOOKUP?

Best Regards,
Marco

I've been working with databases for over 20 years. I've been working
with
Access for 9 years.

I don't know of a way to do what you are asking. The query I posted
should
give you a sum of the time expended by date by employee. One problem is
that if the In and Out times are on different dates then you may get
erroneous durations of time. You can manipulate that sum of minutes -
ElapsedMinutes - in the report using the following as control source to
get
hours and minutes.

=Int([ElapsedMinutes]/60) & ":" & Format([ElapsedMinutes] Mod 60,"00")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi. It seems you are my guides in Access. How lonn you work in Access?

About this topic, is any way to tell the program that the diference is
calculated with something like this: (this would be probably on the
report)

If total records count = 2 then hour_ge (on the record2) - hour_ge (of
the
record1) else
if records count = 4 then (hour_ge (on the record2) - hour_ge (of the
record1))+(hour_ge (on the record4) - hour_ge (of the record3))
end if

Is this possible?

Regards,
Marco




:

Tricky part here is making sure your data is good. That is for every
out
there is an in that corresponds to the correct date and time.

You could T R Y this untested query.

SELECT EmployeeNumber, [Date],
Sum( DateDiff("n",(SELECT Max([Date] + [Time])
FROM YourTable as T
WHERE T.EmployeeNumber = YourTable.EmployeeNumber
AND T.Description = "IN"
AND T.Date + T.Time <= YourTable.Date + YourTable.Time)
,[Date]+[Time]))
as ElapsedMinutes
FROM YourTable
WHERE Description = "Out"
GROUP BY EmployeeNumber, [Date]
ORDER BY EmployeeNumber



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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