Is this even possible using SQL statement in QlikView script?

[#1]

Below are example of data collected from a gantry door: (came from a single database table from MS Access [DoorAccess] but i split them out in QlikView)

Entry table

EntryDate	StaffName	TimeIn	Message
21/11/12	John	07:00:00	IN
21/11/12	Peter	08:10:00	IN
21/11/12	John	09:20:00	IN
21/11/12	Mary	07:20:00	IN
22/11/12	Joe	07:00:00	IN
22/11/12	Mike	08:00:00	IN
23/11/12	John	07:00:00	IN
23/11/12	Mary	07:00:00	IN
23/11/12	Mary	08:00:00	IN
23/11/12	Mary	09:00:00	IN

Exit table

ExitDate	StaffName	TimeOut	Message
21/11/12	Peter	18:10:00	OUT
21/11/12	John	18:00:00	OUT
21/11/12	Peter	18:17:00	OUT
21/11/12	Mary	19:10:00	OUT
22/11/12	Joe	18:20:00	OUT
23/11/12	John	18:10:00	OUT
23/11/12	Mary	17:30:00	OUT

What i wanted:

Base on the two tables i need to derive the hours each staff spent time inside a certain area. However there are alot of noise data which i need clean up.

There may have numerous timeIn or timeOut from the same person each day, so i want to get the earliest time in as the time In and the latest time out as the timeOut. Using the date and staff name as comparison and as identifier, i hope to achieve something like this in qlikview.

The end result should be similar to this:

Date	StaffName	TimeIn	TimeOut	Hours
21/11/12	Peter	08:10:00	18:17:00	10:07
21/11/12	John	07:00:00	18:00:00	11:00
21/11/12	Mary	07:20:00	19:10:00	11:50
22/11/12	Joe	07:00:00	18:20:00	11:20
22/11/12	Mike	08:00:00	Unknown	Unknown
23/11/12	John	07:00:00	18:10:00	11:10
23/11/12	Mary	07:00:00	17:30:00	10:50

I know my question is very demanding but i really tried to achieve this for days. Try using subqueries, join, creation of third table but failed. Not familiar with syntax of Qlikview SQL. I really need help. Be it solutions or alternatives, please suggest. And i do not have DB rights so i cant do much on the database side, mainly on qlikview.

God please help me,

10e5x

[#2]

Thats weird....yours can mine cant:( Did you change any settings or any other thigs?

Btw since we are at it, if u realize i have some logic loopholes. What i have done is only logical for staff that clocks in and out on the same day. Cuz the door is open for 24 hours, and staff do have 24 hour shift so they may clock in at 30/04/12 23:00:00 hours and clock out the next day.... Do you have any suggestions to retify this?

Thanks alot! really u are my saviour

Gratitude,

10e5x

[#3]

Hi Johannes,

With the same qvw u have, what should i do if i would like to display the field message_x. If i declare it at the select staement, i will need to group it too, which will spoilt the current function of the app.

Any suggestion?