Min and Max date

[Rudi]

Hi.

I have a table named "Log" that stores: RecordID (AutoNumber),
LogInTime (Date), LogOutTime (Date). I have a form named "LogIn" that I
run (with an AutoExec macro) every time the application starts. It
collects the LogInTime and stays open (invisible) so that it records
the LogOutTime when the user closes the application. This form fills my
Log table.

Now I would like to create a query that would sort the data from the
Log table. I want the query to display the first and the last LogIn and
LogOutTime for each day.

So if the Log table records look something like this:

RecordID I LogInTime I LogOutTime
1 14.8.2006 10:03:11 14.8.2006 12:33:23
2 14.8.2006 13:01:01 14:8:2006 16:30:00
3 15.8.2006 10:05:04 15.8.2006 15:44:35
4 16.8.2006 10:32:44 16.8.2006 14:22:23
5 16.8.2006 15:03:11 16.8.2006 17:02:22

I want the query to display:

MinLogInTime I MaxLogOutTime I Hours
14.8.2006 10:03:11 14.8.2006 16:30:00 6
15.8.2006 10:05:04 15.8.2006 15:44:35 5,5
16.8.2006 10:32:44 16.8.2006 17:02:22 6,5

Can anyone please help me to make this work?


Regards,

Rudi

 

[John Spencer]

SELECT DateValue(LoginTime) as TheDate
, Min(LoginTime) as LogStart
, Max(LogoutTime) as LogEnd
, DateDiff("n",Min(LoginTime),Max(LogoutTime)) as ElapsedMinutes
, DateDiff("n",Min(LoginTime),Max(LogoutTime)) / 60 as ElapsedHours
FROM YourTable
GROUP BY DateValue(LoginTime)

 

[Rudi]

Thank you very much!

John Spencer je napisal:

SELECT DateValue(LoginTime) as TheDate
, Min(LoginTime) as LogStart
, Max(LogoutTime) as LogEnd
, DateDiff("n",Min(LoginTime),Max(LogoutTime)) as ElapsedMinutes
, DateDiff("n",Min(LoginTime),Max(LogoutTime)) / 60 as ElapsedHours
FROM YourTable
GROUP BY DateValue(LoginTime)