Pairing Issue

Hello,

I have a table that contains rows which are either a type logon or logoff (this is determined by the column 'Type', which is either 'LogOn' or 'LogOff').

Other columns are LogOnID (an autonumber, primary key), UserID and Time.

I would like to pair log ons with log offs for users in a given day.

I am currently using the following query:

SELECT
l1.UserID, l1.Time AS 'Log On', l2.Time AS 'Log Off'
WHERE
l1.UserID = l2.UserID
AND l1.Type = 'LogOn' AND l2.Type = 'LogOff'
AND l1.Time > '2008-05-12 00:00:00.000' AND l1.Time < '2008-05-12 23:59:59.000'
AND l2.Time > '2008-05-12 00:00:00.000' AND l2.Time < '2008-05-12 23:59:59.000'

However this is flawed, as if a person logs in multiple times, it will pair each log on with each log off.

Is there a way to put them into pairs so that I get a log on and pair it with the log off that has the smallest time difference between it and the log on? So, for instance, if I have a log on at 12:30, it would pair with the log off at 13:30, rather than 13:45, as the 13:30 is the closer one, and so must be the one associated with that log on.

It gets even worse, too, because it is possible to have multiple logins in a row without a log off (so it could go log in, log in, log in, log off, log in, log off, for instance), but it should pair the LAST login with the log off (if you see what I mea, in the instance before, the third log in should be associated with the log off).

It seems like a tall order, and I don't know how to do it myself. Perhaps I could pair them based on the smallest difference between the ID, as this is just an incrementing number. Again, I wouldn't know how to do this.

If anyone could shed any light on how this might be possible I would appreciate it.

Thanks,

Simon