Very difficult qns!!! How to derive entry and exit time from 1 messy db.

Discuss Very difficult qns!!! How to derive entry and exit time from 1 messy db. in the SQL Basics forum on Tutorialized. Very difficult qns!!! How to derive entry and exit time from 1 messy db. SQL Basics forum covering the Structured Query Language standard. SQL is a common language recognized by many RDBMS software packages, that is designed to be a robust method for addressing the data stored in a database.

Very difficult qns!!! How to derive entry and exit time from 1 messy db.

Hi all,
I am faced with a very troubled scenario. I am given a .mdb from access 2003. The data are so messy and i am tasked to derive the working hrs. After trying for quite some time, i still unable to get it. Therefore have to post it here, hoping that some pro here can help me derive the sql statements.

TACS table:
EventDate StaffName EventTime Message
21/11/12 John 23:01:00 OUT
21/11/12 Peter 23:10:00 IN
21/11/12 Peter 23:10:05 IN
22/11/12 John 07:19:00 IN
22/11/12 Peter 07:20:00 OUT
22/11/12 Joe 07:30:00 IN
22/11/12 Mike 08:00:00 IN
22/11/12 John 17:00:00 OUT
22/11/12 John 17:01:00 OUT
22/11/12 Joe 19:01:00 OUT
22/11/12 Mike 19:30:00 OUT
22/11/12 Mary 22:00:00 IN
23/11/12 John 07:01:00 IN
23/11/12 Mary 08:00:00 OUT
23/11/12 Peter 09:00:00 IN

My logic is that first check the message is it in or out. In signifies clockIn. If there are multiple clock in before out, i will use the latest clockIn as EntryDT, and vice versa, use the latest clockOut as ExitDT. However due to the mess, i dk how to write a statement to allow the prog to know which timing are for which event. Lastly if there are no respective clock in or out, it will be left unknown.