time query for vb6 and msaccess

Query for VB6 and MS Access

Table:-

         User Id       LogDate       LogTime 

            1            1/1/2010      9:00  

            1            1/1/2010     10:00   

            1            1/1/2010     11:29  

            1            2/1/2010     10:00

            2            2/1/2010     22:00

            2            3/1/2010    11:00

Need 开发者_开发技巧to display as:-

             User Id   LogDate   LogTime   LogDate     LogTime   

             1       1/1/2010  9:00    1/1/2010        10:00   

             1       1/1/2010  11:29   2/1/2010        10:00  

             2       2/1/2010  22:00   3/1/2010        11:00     

---

You need to use a sub-query to find the next date for a user, something like:

SELECT tblStackOverflowTimeQuery.lngUserId, tblStackOverflowTimeQuery.datLogDateTime,
  (SELECT TOP 1 tblStackOverflowTimeQuery2.datLogDateTime
   FROM tblStackOverflowTimeQuery AS tblStackOverflowTimeQuery2
   WHERE tblStackOverflowTimeQuery.lngUserId = tblStackOverflowTimeQuery2.lngUserId
     AND tblStackOverflowTimeQuery2.datLogDateTime > tblStackOverflowTimeQuery.datLogDateTime
   ORDER BY tblStackOverflowTimeQuery2.datLogDateTime
  ) AS datEndDateTime
FROM tblStackOverflowTimeQuery
ORDER BY tblStackOverflowTimeQuery.lngUserId, tblStackOverflowTimeQuery.datLogDateTime;

That'll give the following:

lngUserId   datLogDateTime          datEndDateTime
1           01/01/2010 09:00:00 01/01/2010 10:00:00
1           01/01/2010 10:00:00 01/01/2010 11:29:00
1           01/01/2010 11:29:00 02/01/2010 10:00:00
1           02/01/2010 10:00:00 
2           02/01/2010 22:00:00 03/01/2010 11:00:00
2           03/01/2010 11:00:00 

which is not exactly what you wanted, but it's a start.

It would be easiest to do these things using code, but that would depend on how you want to use it. Presumably there's nothing in your data to indicate which entry is the start and which is the end.