Second highest value

[n90]

So I have a table that has basically the history of Statuses of Job Numbers until the status is set to closed(this includes information like date/who changed the status etc).

And what I am trying to do is run a query that will group by Job Number and have the second highest date, which will in turn allow me to show when the Job Status was last changed and who changed it.

But this is looking like its more difficult than I thought.

 

[jdraw]

see this for ideas
http://www.accessforums.net/programming/get-one-before-last-date-vba-39764.html

 

[CJ_London]

try this psuedocode

SELECT DISTINCT JobNumber, JobDate, JobChangedBy, (SELECT Max(JobDate) FROM tblStatus as Tmp where JobNumber=tblStatus.JobNumber AND JobDate<tblStatus.jobDate) as LastDateChanged FROM tblStatus

 

[n90]

try this psuedocode

SELECT DISTINCT JobNumber, JobDate, JobChangedBy, (SELECT Max(JobDate) FROM tblStatus as Tmp where JobNumber=tblStatus.JobNumber AND JobDate<tblStatus.jobDate) as LastDateChanged FROM tblStatus

This works however it is giving me the second highest date and ALL previous ones. I just need the second highest date.

 

[CJ_London]

Can you post some examples of your data and the result you want to see, I don't understand what it is your require - I thought I did