In & Out Time Query

[samirshah172]

Hello Forum,

I am trying to make a query Which Shows each persons in & out time of a same date, out of Several Entries of each user.

I have a table1 With Fields UserID, DateTime Which Contain data as per below:
UserID DateTime
1 13/09/2015 17:55:25
1 14/09/2015 11:56:30
1 14/09/2015 11:57:03
1 14/09/2015 11:58:10
2
3
3
1 14/09/2015 17:55:56

I Want the Query to Show: DateWise, Each Users, Each dates LowestTime as IN & HigestTime as out. Users Are Punching many times in a day.

I am stuck here, please help.

Regards
Samir

 

[Minty]

You need to create a totals query, group on date and userID. Then Min(DateTime) for your in time and Max(DateTime) for your out time.

I would also consider changing the field name from DateTime as in SQL thats a data type.

If you can't get it working post up the SQL of the query.

 

[samirshah172]

Thanks Minty for prompt reply,

I have never used total Query but I will try and update.

The field name i mentioned is not actual field name.

Thanks
Regards
Samir

 

[arnelgp]

SELECT DISTINCT table1.UserID, Format([DateTime],"Short Date") AS [Date], Format(DMin("DateTime","tblDateTime","[UserID] = " & [UserID] & " And Format([DateTime],'mmddyyyy') ='" & Format([DateTime],"mmddyyyy") & "'"),"Long Time") AS [In], Format(DMax("DateTime","tblDateTime","[UserID] = " & [UserID] & " And Format([DateTime],'mmddyyyy') ='" & Format([DateTime],"mmddyyyy") & "'"),"Long Time") AS [Out]
FROM table1
ORDER BY tblDateTime.UserID, Format([DateTime],"Short Date");

 

[samirshah172]

Thanks Arnelgp,

I am getting error "ORDER BY Clause (tblDateTime.UserID) conflicts with UserID"

my DB is attached

 

[arnelgp]

sorry about that, i am unable to download your db becoz i am experiencing problem with my internet right now.

I am getting error "ORDER BY Clause (tblDateTime.UserID) conflicts with UserID"

just replace tblDateTime in the above statement with your table name (i believe its table1).

 

[gemma-the-husky]

from the data you showed us, how can you distinguish between a clock in and a clock out action?

 

[samirshah172]

For each userID, The First(Lowest Time of a day) entry is IN & Last is Out of each day. Other entry will be ignored of a same user.

 

[samirshah172]

Ok Arnelgp, Its Worked perfect as required. thanks all