Lookup previous value for employee

[essexguy2015]

Hi all

I currently have a table where we are logging a task number against a date and time for each employees to track activity

What I am looking to do is in a query have access lookup the previous reference number for the employee before the date/time of the individual record but on the same day

example
Staff 1 08/08/2015 12:00 Task Ref: 1234
Staff 1 09/08/2015 14:00 Task Ref: 3456
Staff 1 09/08/2015 15:00 Task Ref: 7891

to show something like
Staff 1 08/08/2015 12:00 Ref:1234 Previous Ref: Null
Staff 1 09/08/2015 14:00 Ref: 3456 Previous Ref: Null
Staff 1 09/08/2015 15:00 Ref: 7891 Previous Ref: 3456

Fields are
Employee
DTActStart - this is when task started
DTActEnd - this is when activity ended
Task ID - Reference for task (unique field)

I've tried seaching forums (Used DMax and Lookup) but doesnt find the most recent record before the in view record

I'm thinking a query is the best way so that when Print a report of tasks run of tasks will then show

 

[pbaldy]

Sounds like this, with an additional criteria on date:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[essexguy2015]

Thanks

I had been looking at this but not sure where I am going wrong

the results show as
Start End Ref Employee Previous Ref
06:29 07:14 VP1 Patricica
07:15 08:15 SL1 Patricia VP1
08:16 08:46 PS1 Patricia VP1

and so on showing VP1 for the remaining tasks rather than
Start End Ref Employee Previous Ref
06:29 07:14 VP1 Patricica
07:15 08:15 SL1 Patricia VP1
08:16 08:46 PS1 Patricia SL1
etc etc

The SQL is
SELECT tblTasks.TaskID, tblTasks.DateA, tblTasks.DTActStart, tblTasks.DTActEnd, tblTasks.Employee, tblTasks.Ref,
(SELECT TOP 1 Dupl.Ref
FROM tblTasks AS Dupl
WHERE Dupl.Employee = tblTasks.Employee
AND Dupl.DTActStart < tblTasks.DTActStart
AND Dupl.DateA = tblTasks.DateA
ORDER BY Dupl.TaskID DESC, Dupl.Ref) AS PrevRef,
FROM tblTasks

 

[pbaldy]

I suspect you need to add the time to the order by clause.