OLEDB query to select first and last time for id

I'm using ADODB connection in VBA excel file with Microsoft.Jet.OLEDB.4.0 provider. The file with which I'm establishing connection is .csv file which looks like:

Date and time, Last name, First name
2011-08-29  05:48:50,lname1,fname1
2011-08-29  05:49:50,lname1,fname1
2011-08-29  05:55:50,lname2,fname2
2011-08-29  16:11:50,lname1,fname1
2011-08-29  17:55:50,lname2,fname2
2011-08-30  9:11:50,lname1,fname1

The point is that my data is sorted by Date and time which is in one field, and user names are not in any order. What I really need to do is create a query to fill Recordset.

I need this query to select first, second and last hour for each day for each user name. Is it even possible to split Date and time column just using a query? I've got general idea how to select what I want, but the thing is too complicated for me, because of that Date and time in the same field.

Would you give me any suggestions?

Solution

Let us say your file is called Dates.csv, you can try:

SELECT 
   [Last name] & ", " & [First Name] AS FullName, 
   Format([Date And time],"yyyy/mm/dd") AS WorkingDay, 
   First(Format([Date And time],"hh:nn:ss")) AS FirstHour, 
   Last(Format([Date And time],"hh:nn:ss")) AS LastHour
FROM [Dates.csv]
GROUP BY [Last name] & ", " & [First Name], Format([Date And time],"yyyy/mm/dd")

I am a little suspicious of the double space between date and time. I would get rid of the spaces in the column names, if I were you. It will make things easier.