I have 2 tables tblJob & tblJobSession, pic attached
I would like to show all Jobs with open jobsessions , but only the last visit
so out of the table tblJobSession i would like to only show RecID 2 and 3
data to return would be
tblJob.JobID , tblJob.Reg , tblJob.JobType , tblJobSession.VisitDate , tblJobSession.Engineer , tblJobSession.Status
can some one help. I thought i had it with
SELECT tblJobSession.VisitNo, tblJobSession.JobID, tblJobSession.VisitDate, tblJobSession.Engineer, tblJobSession.Status, tblJob.Reg, tblJob.Status
FROM tblJob INNER JOIN tblJobSession ON tblJob.JobID = tblJobSession.JobID
WHERE (((tblJobSession.VisitNo)=(select max(tblJobSession.visitNo) from tblJobSession)));
but this does not show the 2 records i want , it only shows the highest
tks.
I would like to show all Jobs with open jobsessions , but only the last visit
so out of the table tblJobSession i would like to only show RecID 2 and 3
data to return would be
tblJob.JobID , tblJob.Reg , tblJob.JobType , tblJobSession.VisitDate , tblJobSession.Engineer , tblJobSession.Status
can some one help. I thought i had it with
SELECT tblJobSession.VisitNo, tblJobSession.JobID, tblJobSession.VisitDate, tblJobSession.Engineer, tblJobSession.Status, tblJob.Reg, tblJob.Status
FROM tblJob INNER JOIN tblJobSession ON tblJob.JobID = tblJobSession.JobID
WHERE (((tblJobSession.VisitNo)=(select max(tblJobSession.visitNo) from tblJobSession)));
but this does not show the 2 records i want , it only shows the highest
tks.