Help adding time events in MS Access

[Scottie]

I have a table that contains different login and logout times along
with codes. 2 for login and 4 for logout. I need to be able determine
the time duration between each login and logout event. There can be
multiple login and logouts during a 24 hour period. I have attached
the table for convenience.

Time Conv USER WST_NR Login/out Code Event Code
2:20:16 AM MSP2SXS US05519113 2 42
2:36:24 AM MSP2SXS US05519113 4 42
2:41:50 AM MSP2SXS US05519113 2 42
2:43:32 AM MSP2SXS US05519113 4 42
4:21:36 AM MSP2SXS US05519113 2 42
4:22:37 AM MSP2SXS US05519134 4 42
6:09:28 AM MSP2SXS US05519140 2 42
7:17:07 AM MSP2SXS US05519140 4 42

Any help would be greatly appreciated.



Scott

 

[Wayne Morgan]

Include the date with the time, this way if it goes across midnight, you
will still get accurate results without having to do a lot of work to test
for this.

To find a logout that follows a login, search for the smallest time that is
greater than or equal to the login time. You may find the DMin() function a
good one for this.

You may also find this query handy. It has 2 calculated fields that give the
associated log out time and the elapsed time in seconds for each login.

SELECT LoginLogout.LogTime, DMin("LogTime","LoginLogout","LogTime > #" &
[LogTime] & "# And UserName = """ & [UserName] & """") AS LogOut,
DateDiff("s",[LogTime],[LogOut]) AS ElapsedTime, LoginLogout.UserName,
LoginLogout.LoginOutCode
FROM LoginLogout
WHERE (((LoginLogout.LoginOutCode)=2));

 

[Wayne Morgan]

PS, minor correction on the query.

SELECT LoginLogout.LogTime, DMin("LogTime","LoginLogout","LogTime >= #" &
[LogTime] & "# And UserName = """ & [UserName] & """ And LoginOutCode = 4")
AS LogOut, DateDiff("s",[LogTime],[LogOut]) AS ElapsedTime,
LoginLogout.UserName, LoginLogout.LoginOutCode
FROM LoginLogout
WHERE (((LoginLogout.LoginOutCode)=2));

 

[Scottie]

Thanks for answering my question. I am having difficulty discerning
the syntax with some of the code. Specifically, the field names. Here
is the SQL that I have currently. Could you show me how to adjust my
code accordingly?

SELECT Time.USR_SES_STS_TS, Time.USR_NR, Time.WST_NR,
Time.USR_LGN_STS_CD, Time.SRT_TYP_CD, Time.PRC_TYP_CD
FROM [Time];


Thanks for your help,
Scott

 

[Wayne Morgan]

You will need brackets around "Time" in every case, it is a reserved word.
The fields needed for the SQL that I used are the field with the login/out
time, the field with the user's name or id, and the field describing whether
the time is a login or log out. I then created 2 calculated fields, as
denoted by the word "As" in the SQL. The SQL will return the associated log
out of every login on the same line as the login. It also calculates the
elapsed time.

If you let me know which of the fields you have listed are the 3 I mention
above, I'll adjust your SQL. The fields I show you have are:

Table [Time] Fields:
USR_SES_STS_TS
USR_NR
WST_NR
USR_LGN_STS_CD
SRT_TYP_CD
PRC_TYP_CD

 

[Scottie]

Wayne,

I think that I figured out the syntax. Here is what I came up with and
the results.
SELECT LoginLogout.LogTime, DMin("LogTime","LoginLogout","LogTime >= #"
& [LogTime] & "# And UserName = """ & [UserName] & """ And LoginOutCode
= 4") AS LogOut, DateDiff("s",[LogTime],[LogOut]) AS ElapsedTime,
LoginLogout.UserName, LoginLogout.LoginOutCode

FROM LoginLogout
WHERE (((LoginLogout.UserName)="msp2sxs") AND
((LoginLogout.LoginOutCode)=2));

LogTime LogOut ElapsedTime UserName LoginOutCode
12/22/2005 3:16:38 AM 12/22/2005 3:20:38 AM 240 MSP2SXS
2
12/22/2005 3:21:54 AM 12/22/2005 3:25:42 AM 228 MSP2SXS
2
12/22/2005 3:27:55 AM 12/22/2005 3:34:49 AM 414 MSP2SXS
2
12/22/2005 5:39:47 AM 12/22/2005 5:48:54 AM 547 MSP2SXS
2
12/22/2005 5:49:04 AM 12/22/2005 5:52:16 AM 192 MSP2SXS
2
12/22/2005 5:49:22 AM 12/22/2005 5:52:16 AM 174 MSP2SXS
2

My times are in GMT, so they are off by 6 hours. How do I subtract 6
hours from these timestamps? Do I need to use TimeValue and then
DateAdd?

Scott

 

[Wayne Morgan]

To subtract 6 hours, use DateAdd. You won't need TimeValue for that. Using
DateAdd will make the date/time fields calculated fields also.

Example:
SELECT LoginLogout.LogTime

would be

SELECT DateAdd("h", -6, [LoginLogout].[LogTime]) As LoginTime

However, this will change depending on whether or not you're currently in
Daylight Savings Time. There is a Windows API call you can make to determine
whether or not you are currently in DST and adjust the difference
accordingly.

Private Declare Function GetTimeZoneInformation Lib "kernel32"
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(0 To 63) As Byte
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(0 To 63) As Byte
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Private Const TIME_ZONE_ID_INVALID = &HFFFFFFFF
Private Const TIME_ZONE_ID_UNKNOWN = 0
Private Const TIME_ZONE_ID_STANDARD = 1
Private Const TIME_ZONE_ID_DAYLIGHT = 2

Public Sub TimeInfo()
Dim nRet As Long
Dim tz As TIME_ZONE_INFORMATION

nRet = GetTimeZoneInformation(tz)
If nRet <> TIME_ZONE_ID_INVALID And nRet <> TIME_ZONE_ID_UNKNOWN Then
Debug.Print "Current Bias: " & tz.Bias / 60 - (nRet - 1)
End If
End Sub

The bias will give you the amount of time to add to the local time to reach
UTC (in minutes). Divide by 60 to get hours. If you are daylight time nRet
will be 2, standard time nRet will be 1. Subtract 1 from this value then
subtract that from the bias to get the current bias (daylight or standard
time).

--
Wayne Morgan
MS Access MVP

Wayne,

I think that I figured out the syntax. Here is what I came up with and
the results.
SELECT LoginLogout.LogTime, DMin("LogTime","LoginLogout","LogTime >= #"
& [LogTime] & "# And UserName = """ & [UserName] & """ And LoginOutCode
= 4") AS LogOut, DateDiff("s",[LogTime],[LogOut]) AS ElapsedTime,
LoginLogout.UserName, LoginLogout.LoginOutCode

FROM LoginLogout
WHERE (((LoginLogout.UserName)="msp2sxs") AND
((LoginLogout.LoginOutCode)=2));

LogTime LogOut ElapsedTime UserName LoginOutCode
12/22/2005 3:16:38 AM 12/22/2005 3:20:38 AM 240 MSP2SXS
2
12/22/2005 3:21:54 AM 12/22/2005 3:25:42 AM 228 MSP2SXS
2
12/22/2005 3:27:55 AM 12/22/2005 3:34:49 AM 414 MSP2SXS
2
12/22/2005 5:39:47 AM 12/22/2005 5:48:54 AM 547 MSP2SXS
2
12/22/2005 5:49:04 AM 12/22/2005 5:52:16 AM 192 MSP2SXS
2
12/22/2005 5:49:22 AM 12/22/2005 5:52:16 AM 174 MSP2SXS
2

My times are in GMT, so they are off by 6 hours. How do I subtract 6
hours from these timestamps? Do I need to use TimeValue and then
DateAdd?

Scott