Relationship based on date/time range - how to pull this off 1

[keun]

I have two tables that I want to relate.

tbl_1 contains USERID, DATE, TIME, LOGIN_LOGOUT

tbl_2 contains USERID, DATE, TIME, EVENT_DESCRIPTION

For example:

tbl_l
c00014;12/23/2013;12:07:22;login
c00014;12/23/2013;12:12:08;logout
c00014;12/23/2013;12:20:23;login
c00014;12/23/2013;12:25:07;logout

tbl_2
c00014;12/23/2013;11:11:47;Activity Description 7
c00014;12/23/2013;11:11:59;Activity Description 2
c00014;12/23/2013;12:08:46;Activity Description 4
c00014;12/23/2013;12:08:46;Activity Description 7
c00014;12/23/2013;12:09:21;Activity Description 6
c00014;12/23/2013;12:20:20;Activity Description 1
c00014;12/23/2013;12:20:57;Activity Description 1
c00014;12/23/2013;12:20:57;Activity Description 1
c00014;12/23/2013;12:21:05;Activity Description 3
c00014;12/23/2013;12:23:46;Activity Description 4
c00014;12/23/2013;12:27:05;Activity Description 8
c00014;12/23/2013;12:27:19;Activity Description 5
c00014;12/23/2013;12:28:24;Activity Description 8
c00014;12/23/2013;12:28:33;Activity Description 5


What I want to do is to assign a "SessionID" to create sessions in tbl_1 based on the login/logout times (I can do this part) and then query tbl_2 based on time and date in order to determine the "SessionID" during which the activity took place.

The tables have multiple users and multiple days. Any thoughts?

Basically, this is how the sample tables will relate.

 

[dhookom]

I can't force myself to use reserved words as fields names so I changed them in my test database.
First create a query [qselLogInOut] with the following SQL:

SELECT (SELECT Count(*) FROM tbl_1 Copy WHERE Copy.UserID = tbl_1.UserID AND 
  copy.Login_Logout = "login" AND copy.TheDate + copy.TheTime <= tbl_1.TheDate + tbl_1.TheTime) AS SessionID, 
tbl_1.UserID, tbl_1.TheDate, tbl_1.TheTime, tbl_1.Login_Logout, [TheDate]+[TheTime] AS LogIn, 
(SELECT Min(TheDate+TheTime) FROM tbl_1 copy
 WHERE copy.UserID = tbl_1.UserID AND copy.TheDate + copy.TheTime > tbl_1.TheDate + tbl_1.TheTime) AS LogOut
FROM tbl_1
WHERE tbl_1.Login_Logout="LogIn";

Results will be:

[b]SessionID  UserID   TheDate      TheTime        Login_Logout  LogIn                     LogOut[/b]
1          c00014   12/23/2013   12:07:22 PM    login         12/23/2013 12:07:22 PM    12/23/2013 12:12:08 PM
2          c00014   12/23/2013   12:20:23 PM    login         12/23/2013 12:20:23 PM    12/23/2013 12:25:07 PM

Then create another query with SQL of:

SELECT qselLogInOut.SessionID, tbl_2.UserID, [tbl_2].[TheDate]+[tbl_2].[theTime] AS EventDtTm, tbl_2.TheTime, tbl_2.Event_Description
FROM qselLogInOut INNER JOIN tbl_2 ON qselLogInOut.UserID = tbl_2.UserID
WHERE ((([tbl_2].[TheDate]+[tbl_2].[theTime]) Between [Login] And [logout]));

Duane
Hook'D on Access
MS Access MVP

 

[keun]

Thanks for this, I am going to try it out.

And also thinks for the heads up on the reserved words, I always forget about that and will make appropriate changes.

 

[PHV]

will make appropriate changes
Further more, why TWO columns for a SINGLE DataTime value ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[keun]

Further more, why TWO columns for a SINGLE DataTime value ?
I am working on some logs that are delivered in this format. I am going to do some stuff to normalize the data before I work with it; merging the two fields can be one of the things I do.

I am created the queries and I like what I see. However, qselLogInOut, which creates the Login and Logout fields, is not working correctly. I think it is because the Logout field is generated using a MIN function and that function is pulling a Logout number from another user. If that makes sense. I am going to mock up a little sample so show what is going on.

 

[dhookom]

keun,
If you have trouble, you need to reply back with a copy of your exact SQL.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

I figured out my problem. The Login_Logout field contains values other than "Login" and "Logout" - it has stuff in it like "error" and "password change" etc. I do not need those values so I removed them and things appear to be working.

While this gives me a clue to the answer to my next question, I cannot figure it out. How is qselLogInOut creating the Logout field? I want to understand because I want to be able to troubleshoot issues in the future. The data I have is very clean - I am not missing any login/logout values and each logout value is later in time that the previous login. I am concerned that if I get some messed up data the function it is not going to work. I can QC the inbound data, but I still would like to understand what is happening here. If it can be put into words!

 

[dhookom]

The first query [qselLogInOut] uses a subquery to find the minimum date/time from the same table where the UserIDs are the same and the datetime is higher.

Picture you have two lists of the same records side by side. Use your left index finger to stop at the first record with "Login". Then filter the second list by the same UserID but datetime values higher. Then use your right index finger to find the minimum of these. Based on your reported issue, you could have also filtered the second copy of the list for "Logout".

The query does this for every Login record.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

OK! I see that now. Very cool.

Let me ask you one more question - is there something about this query that is processor-heavy? I can run the final query and get results but I cannot export it or turn it into a Make Table. By "cannot" I mean that my computer is just hanging. I am running it on an i3 and will try it on a more powerful computer tonight but I thought I would ask what you thought. tbl_2 has about 150,000 rows so I can see things taking a long time but I might come back and ask about ways to speed this thing up.

 

[dhookom]

This type of query can be very expensive. Which fields are indexed in your table? A combination of the Date/Time fields should be indexed as well as individual indexes on Login_Logout and UserID.

The SessionID might not be needed for anything. If this doesn't increase the performance, you might need to send the results of the first query to a table to use in the second query.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

I do not have any fields indexed. I will try those changes but I was able to get the query to export by making the first query a table, so that is great.

Thanks for the help with this!

 

[dhookom]

Learn about indexes.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

I made some modifications to the table and now I cannot get part of the query to work. It's that part that identifies the Logout Date and Time.

I have merged my date and time fields into a date field called DateFormatted, it is formatted as yyyy-mm-dd hh:mm:ss

I rewrote the query to the following and the Logout field does not populate; I get correct values for the SessionID and LogIn. I am pasting the original code and my new code here for ease of comparison. Can anyone tell me why LogOut is not populating?


OLD CODE (This works but my date and time are no longer separate)

SELECT (SELECT Count(*) FROM tbl_1 Copy WHERE Copy.UserID = tbl_1.UserID AND 
  copy.Login_Logout = "login" AND copy.TheDate + copy.TheTime <= tbl_1.TheDate + tbl_1.TheTime) AS SessionID, 
tbl_1.UserID, tbl_1.TheDate, tbl_1.TheTime, tbl_1.Login_Logout, [TheDate]+[TheTime] AS LogIn, 
(SELECT Min(TheDate+TheTime) FROM tbl_1 copy
 WHERE copy.UserID = tbl_1.UserID AND copy.TheDate + copy.TheTime > tbl_1.TheDate + tbl_1.TheTime) AS LogOut
FROM tbl_1
WHERE tbl_1.Login_Logout="LogIn";

NEW CODE

SELECT ((SELECT Count(*) FROM tbl_1 Copy WHERE Copy.UserID = tbl_1.UserID AND
    copy.Login_Logout = "login" AND DateFormatted <= DateFormatted) & [UserID] & Format([DateFormatted],'yyyymmdd')) AS SessionID,
	tbl_1.UserID, tbl_1.DateFormatted, tbl_1.Login_Logout, tbl_1.DateFormatted AS LogIn,
	(SELECT Min (DateFormatted) FROM tbl_1 copy  
	WHERE copy.UserID = tbl_1.UserID AND copy.DateFormatted > DateFormatted) AS LogOut
FROM tbl_1
WHERE (((tbl_1.Login_Logout)="LogIn"));

Don't get thrown off by the slight change to the SessionID field, I added date so that I could get a unique value for each session. As I said, this part of the query is fine.

 

[dhookom]

I would make sure you always keep the table.field or alias.field syntax in your queries, especially in the subqueries. Also there seems to be a space after Min.

Duane
Hook'D on Access
MS Access MVP

 

[keun]

POW. That fixed it. Taking care of the table.field set me straight.