The two tables are joined on the common fields. Those are AccessDate,StaffName and AccessResult. That is probably not what you want. Because AccessResult is different in the two tables, no records are matched. So what happens is this:
Entry AccessDate EntryDate TimeIn Staffname AccessResult 01-01-2012 01-01-2012 8:00 John Legal Access 02-01-2012 02-01-2012 9:00 Jack Legal Access Exit AccessDate ExitDate TimeOut Staffname AccessResult 01-01-2012 01-01-2012 9:00 John Legal Exit 02-01-2012 02-01-2012 10:00 Jack Legal Exit Join result: AccessDate EntryDate TimeIn Staffname AccessResult ExitDate TimeOut 01-01-2012 01-01-2012 8:00 John Legal Access - - 02-01-2012 02-01-2012 9:00 Jack Legal Access - - 01-01-2012 - - John Legal Exit 01-01-2012 9:00 02-01-2012 - - Jack Legal Exit 02-01-2012 10:00
The solution is to make sure the tables are joined on the right key. In your case I think you want to match EntryDate to ExitDate. It would help if an access (event) has a unique identifier like EventID. Then you could match on only that key. You'll have to make sure that any fields that shouldn't be used to join on have different names in the two tables.
Thanks for the reply. Yes your are right and i solved that by redoing the Seelct statement with a join.(with the help of Joahannes for QlikView).
Since we are at it, i hope u can help me with my other problems... the more urgent one is getting the employees who work more than X amount of hours on Y date.
X and Y are to be selected by the user...
How should i go about doing that? i am guessing input box and set the variable. But i only started using QlikView 2 days ago, and the tutorial on qlikview is too basic(just modify the numbers).