datediff() using fields from different records

[tdb]

I am trying to calculate the session time for logon and logoff datetime fields, but the fields are in different records and different columns. Is this possible with a subquery? Here's a sample of the non-sequential fields and no sequencing field:

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

I am not sure how to, or even if it;s necessary, to get the datetime fields in the same record. Any help is much appreciated.
tdb

[tdb]

the columns did not post correctly - the LogonhostDate field is separate from the LogoffhostDate field so that each record has one or the other, but never both so that for example: ID 300 is logon date and 301 is logoff date.

[tdb]

clarification - different fields from different records

[NeoPa]

Very important question -
Can the records appear in mixed order or will the logoff for 228 ALWAYS be in 229?
Also, can multiple UserIDs be mixed up?
So, can User1 Log on,; followed by User2; then either User1 OR User2 Log off first?

[tdb]

In the original table, the order is mixed, but I sorted on user and ID in a query, then run as a parameter query pulling only one user. Sometimes either logon or logoff does not get entered into database - maybe connection was lost before updating, etc.
(If there is a way to attach a screenshot I can do that, but I don't see a button here.)

[NeoPa]

I've given this some consideration, and it seems to me that there needs to be a session ID in both the Logon & the Logoff entries. Otherwise there is no defined way to tie the two together.
Going by your posted data, there can be multiple sessions for one user in the same table.
I would say a session ID (different from the record ID) is a must for this to work.

Of course, with that it actually becomes quite easy to do.
You can GROUP BY just the session ID if it's completely unique (why not) or add the Useer if it is only unique per user.

[tdb]

I tried to find a sessionID but could not find one in Windows WMI that is unique each time user logs on; the one I found only changes if the server reboots.

If I could get the data to always alternate logon and logoff entries so that it's only the 2 columns to calculate from (without the sessionID) would it be possible to do a loop? So that using the datediff() function the startdate would come from logon column/first field, the end date would come from logoff column/second field and then alternate back and forth between columns for successive calculations?

[NeoPa]

You could do it by processing through a dataset (probably a restricted subset of the table) using VBA code and sequentially trying to match up the entries.
There would be problems though, as the data doesn't necessarily conform to how you want it. The code would have to handle all possible set of the data.
In all, not too straightforward .

[tdb]

How about this layout:
Logtype Datetime
logon 10/19/2006 3:00:00
logoff 10/192006 4:00:00
logon 10/20/2006 12:00:00
logoff 10/20/2006 1:00:00

Is there a way to pull these for the datediff() function?
It seems like it would be Datediff("n", (SELECT tbl.Datetime FROM tbl WHERE tbl.Logtype = logon), (SELECT tbl.Datetime FROM tbl WHERE tbl.Logtype=log off)).

THanks for your help thinking this through.

[MMcCarthy]

In the following function the first recordset is based on a query based on the table you described which I'm calling tablename. The second recordset is exactly the same but with a different sort order.

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

The insert statements are to a new table called tblSessions with the fields SessionID (set to autonumber), user, logonTime, logoffTime.

Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a Microsoft DAO library ticked.

Create the following function in a module:

 
Function buildSessions()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim slogoff As Date
	Set db = CurrentDb
	Set rs1 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogonhostDate")
	Set rs2 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogoffhostDate")
	rs1.MoveFirst
	Do Until rs1.EOF
		If Not IsNull(rs1!LogonhostDate) Then
			rs2.MoveFirst
			Do Until rs2.EOF
				If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
					slogoff = rs2!LogoffhostDate
					rs2.MoveLast
				End If
				rs2.MoveNext
			Loop
			DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
						 "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
		End If
		rs1.MoveNext
	Loop
	
	rs1.Close
	rs2.Close
	Set rs1 = Nothing
	Set rs2 = Nothing
	Set db = Nothing
End Function

If you're not sure how to call this function let me know

[NeoPa]

Sometimes either logon or logoff does not get entered into database - maybe connection was lost before updating, etc.

I apologise if I sound churlish as this is very good code and would work perfectly if all session starts and ends were guaranteed to be logged.
If any are missing, though, this may incorrectly connect session start and end times.

Consider :
User1 ID=239 Logon@09:00
User1 logged off at 09:15 but this was not logged by the system
User1 ID=240 Logon@10:00
User1 ID=241 Logoff@10:30

This would log a session starting at 09:00 and finishing at 10:30 AS WELL AS a session starting at 10:00 and finishing at 10:30.

Using the GIGO rule, it is reasonable to expect some garbage in the results if the data's bad - but you should at least know what to expect.

[MMcCarthy]

I apologise if I sound churlish as this is very good code and would work perfectly if all session starts and ends were guaranteed to be logged.

If any are missing, though, this may incorrectly connect session start and end times.

Are you daring to argue with me <g>

[NeoPa]

NO
:Retreats hastily:

Seriously - I was quite impressed with the code - It's just the data that's a bitch.

[tdb]

Yes, I agree, this looks like it would work and put both the logon and logoff date on the same record so that I could run the datediff( ) funcion. I do need advise on how to call the function. (sorry it took so long to get back to this - we have been moving across the country and I think I got lost in a box somewhere...)
Thanks for your help! :)

In the following function the first recordset is based on a query based on the table you described which I'm calling tablename. The second recordset is exactly the same but with a different sort order.

User ID LogonhostDate LogoffhostDate
test1 228 9/4/2006 8:52:38 PM
test1 229 9/4/2006 9:02:14 PM
test1 230 9/4/2006 9:06:59 PM
test1 231 9/4/2006 11:56:01 PM
test1 232 9/5/2006 12:01:56 AM
test1 233 9/5/2006 12:46:06 AM

The insert statements are to a new table called tblSessions with the fields SessionID (set to autonumber), user, logonTime, logoffTime.

Make sure the DAO library is available. In the VB editor window go to Tools - References and make sure that there is a Microsoft DAO library ticked.

Create the following function in a module:

 
Function buildSessions()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim slogoff As Date
	Set db = CurrentDb
	Set rs1 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogonhostDate")
	Set rs2 = db.OpenRecordset("SELECT * FROM tablename ORDER BY LogoffhostDate")
	rs1.MoveFirst
	Do Until rs1.EOF
		If Not IsNull(rs1!LogonhostDate) Then
			rs2.MoveFirst
			Do Until rs2.EOF
				If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
					slogoff = rs2!LogoffhostDate
					rs2.MoveLast
				End If
				rs2.MoveNext
			Loop
			DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
						 "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
		End If
		rs1.MoveNext
	Loop
	
	rs1.Close
	rs2.Close
	Set rs1 = Nothing
	Set rs2 = Nothing
	Set db = Nothing
End Function

If you're not sure how to call this function let me know

[MMcCarthy]

Yes, I agree, this looks like it would work and put both the logon and logoff date on the same record so that I could run the datediff( ) funcion. I do need advise on how to call the function. (sorry it took so long to get back to this - we have been moving across the country and I think I got lost in a box somewhere...)
Thanks for your help! :)

The easiest way to call the function (Put it in a module by the way) is:

Put a command button on a form. Call it cmdBldSessions and put the following line in code:

 
Private Sub cmdBldSessions_Click()
	buildSessions
End Sub