Where did you go, where did you come from, how long did it take query. Part of the way there!

[keun]

I have data from a user log, it spans multiple days, contains information about multiple users, and records a variety of actions. As user logs are known to do, this data records what a person did and when they did it, over an over. Since I can tell what they did and when they did it, I can tell what they did next. Alternatively, since I know what they did, I can tell what they did before.

I am interested in putting together some analysis around where a person went next, or where they were before they got to a place.

Consider this data:

I can see that "home screen" usually follows "log on" and "view search results" usually follows "search." What I would like to do is create some small "n-grams," as a way of piece-wise hashing the behavior. In other words, for any record, I would like to see where they went next and where they came from. Maybe up to two or three moves forward and backwards if possible (but I will settle for just one at this point).

I know this is possible because PHV just helped me do some of the heavy lifting here.

For starters, can you tell me how to include the "next action" in the results from this query:

SELECT S.userid, S.action, S.DateTime, Format((SELECT Min(D.DateTime)-S.DateTime FROM sheet1 D WHERE D.userid=S.userid AND D.DateTime>S.DateTime),'h:nn:ss') AS DifCalc
FROM sheet1 AS S;

Once I get that done I know where I will go next and I might be able to figure out that piece myself - which is the "previous action" piece.

Background: when I first came up with this idea I was concerned with knowing how long a person stays on a screen before they go to the next screen. My focus was on the screen they were on as the indicator of how long things should take. However, I realize that where they are going is just as important because the time might be more about what they need to do to prepare for the next move rather than some quality of the present place.

I know that going three steps forward or backward is a tougher task because we cannot relay on MIN and MAX, so I am hoping to just get the previous and next action for my immediate analysis.

I joined this forum in 2005. I am still a hack.

 

[PHV]

One way (typed, untested):

SELECT S.userid, S.action, S.DateTime
, Format((SELECT Min(D.DateTime)-S.DateTime FROM sheet1 D WHERE D.userid=S.userid AND D.DateTime>S.DateTime),'h:nn:ss') AS DifCalc
, (SELECT action FROM sheet1 N WHERE N.userid=S.userid AND N.DateTime=(SELECT Min(D.DateTime) FROM sheet1 D WHERE D.userid=S.userid AND D.DateTime>S.DateTime)) AS NextAction
, (SELECT action FROM sheet1 P WHERE P.userid=S.userid AND P.DateTime=(SELECT Max(D.DateTime) FROM sheet1 D WHERE D.userid=S.userid AND D.DateTime<S.DateTime)) AS PrevAction
FROM sheet1 S

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

 

[keun]

I see what you are doing here, and I like it. The problem is that the query runs and does not stop. I have tried it on two computers, one running Access 2007 one running 2010. While it is running Access does not cause a spike in resource-use so I cannot really tell if anything is actually happening.

I joined this forum in 2005. I am still a hack.

 

[keun]

This is working now. I do not know why this would cause a problem, but I had some records where userid and action were blank. I cleaned that stuff up and it works!

I joined this forum in 2005. I am still a hack.