Solved Need help with Query (1 Viewer)

[K_ZEON]

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.

 

[Gasman]

Have you tried a GROUP BY ?

 

[Josef P.]

SELECT
    S.VisitNo, S.JobID, S.VisitDate, S.Engineer, S.Status, J.Reg, J.Status
FROM
    tblJob As J
    INNER JOIN (
        select JobId, Max(visitNo) as MaxVisitNo
        from tblJobSession
        group by JobId
    ) As MaxV ON MaxV.JobID = J.JobID
    inner join
    tblJobSession As S ON S.JobId = J.JobId and S.visitNo = MaxV.MaxVisitNo

 

[K_ZEON]

SELECT
    S.VisitNo, S.JobID, S.VisitDate, S.Engineer, S.Status, J.Reg, J.Status
FROM
    tblJob As J
    INNER JOIN (
        select JobId, Max(visitNo) as MaxVisitNo
        from tblJobSession
        group by JobId
    ) As MaxV ON MaxV.JobID = J.JobID
    inner join
    tblJobSession As S ON S.JobId = J.JobId and S.visitNo = MaxV.MaxVisitNo

tks, just pasted into Access but does not work. I have tried to change the names to the correct tables but not sure what to do with MaxV ?
sorry but i dont really do a lot of sql except basic quesries.
If you could , could you show how i would adjust this to match my tables.

it gives error

Syntax error (missing operator) in query expression 'MaxV.JobID = J.JobID Inner Join tblJobSession as S on S.JobID = J.Jobid'

tks

 

[K_ZEON]

Have you tried a GROUP BY ?

yes i tried group by but not able to get what i need.

 

[Josef P.]

Not thought of: Access needs brackets for the join expressions:

SELECT
   S.VisitNo, S.JobID, S.VisitDate, S.Engineer, S.Status, J.Reg, J.Status
FROM
(
    tblJob As J
    INNER JOIN (
        select JobId, Max(visitNo) as MaxVisitNo
        from tblJobSession
        group by JobId
     ) As MaxV ON MaxV.JobID = J.JobID
)
inner join
tblJobSession As S ON S.JobId = MaxV.JobId and S.visitNo = MaxV.MaxVisitNo

 

[plog]

I would like to show all Jobs with open jobsessions , but only the last visit

That's ambiguous. What does "but only the last visit" mean? Is that part of the criteria? Or what you want to show?

Example A:
RecID, VisitNo, JobID, VisitDate, Status
7, 1, 4, 1/1/2024, open
8, 2, 4, 2/2/2024, completed

Which of the above should be shown? This JobID has 2 records, one of which is open--do you want to see the "last" record (8) or the "last open record" (7)?

Example B:
RecID, VisitNo, JobID, VisitDate, Status
9, 1, 5, 2/2/2024, open
10, 2, 5, 1/1/2024, open

Which of the above should be shown? This JobID has 2 open records--is the "last" record by VisitDate or by VisitNo?

Let me guess you're response to one or both of my examples--"The data couldn't happen like that". To which I preemptively ask--are you sure? Run a query to check if a Job could have a completed visit after an open visit. Then run a query to see if all the VisitDates and VisitNo happen in the correct order.

People's assumptions about their data very rarely align with the reality of data. It's never as clean as you assume it is.

 

[K_ZEON]

Not thought of: Access needs brackets for the join expressions:

SELECT
   S.VisitNo, S.JobID, S.VisitDate, S.Engineer, S.Status, J.Reg, J.Status
FROM
(
    tblJob As J
    INNER JOIN (
        select JobId, Max(visitNo) as MaxVisitNo
        from tblJobSession
        group by JobId
     ) As MaxV ON MaxV.JobID = J.JobID
)
inner join
tblJobSession As S ON S.JobId = MaxV.JobId and S.visitNo = MaxV.MaxVisitNo

tks, works as expected, now to see if i can expand from this example to a bigger query as this was just some of the data.
if i get stuck, i hope i can post again.

tks for the quick help

 

[K_ZEON]

That's ambiguous. What does "but only the last visit" mean? Is that part of the criteria? Or what you want to show?

Example A:
RecID, VisitNo, JobID, VisitDate, Status
7, 1, 4, 1/1/2024, open
8, 2, 4, 2/2/2024, completed

Which of the above should be shown? This JobID has 2 records, one of which is open--do you want to see the "last" record (8) or the "last open record" (7)?

Example B:
RecID, VisitNo, JobID, VisitDate, Status
9, 1, 5, 2/2/2024, open
10, 2, 5, 1/1/2024, open

Which of the above should be shown? This JobID has 2 open records--is the "last" record by VisitDate or by VisitNo?

Let me guess you're response to one or both of my examples--"The data couldn't happen like that". To which I preemptively ask--are you sure? Run a query to check if a Job could have a completed visit after an open visit. Then run a query to see if all the VisitDates and VisitNo happen in the correct order.

People's assumptions about their data very rarely align with the reality of data. It's never as clean as you assume it is.

Hi. tks for the input. I need to show only Jobs that have an open Visit and each time engineer attends site a new visit is added and marked as open.
previous visit is closed.

So the previous code in post #6 does what i need and i will take this example and hopefully expand to include more data from other tables etc.

 

[plog]

If every JobSession can only have 1 open visit and only the last visit can be open, then that query is overkill. No aggregation needed. You simply need to query JobSessions for records with open in the status and JOIN the Job table to it to bring in those fields.

Again though, data assumptions <> data reality.

 

[Josef P.]

"only the last visit" vs "only open visits"

only open visits (only 1 can be open):

SELECT
   S.VisitNo, S.JobID, S.VisitDate, S.Engineer, S.Status, J.Reg, J.Status
FROM
    tblJob As J
    inner join
    tblJobSession As S ON S.JobId = J.JobId
where
    S.Status = 'open'

 

[K_ZEON]

If every JobSession can only have 1 open visit and only the last visit can be open, then that query is overkill. No aggregation needed. You simply need to query JobSessions for records with open in the status and JOIN the Job table to it to bring in those fields.

Again though, data assumptions <> data reality.

sometimes a jobsession could still be open ie engineer has done an update for visit 1 but not fully completed ie closed. could be at Further work.
then a new visit is created which is open but the first visit it not manually closed. In this scenario it would show both visits hence why i only need to show the last open session.

 

[K_ZEON]

"only the last visit" vs "only open visits"

only open visits (only 1 can be open):

SELECT
   S.VisitNo, S.JobID, S.VisitDate, S.Engineer, S.Status, J.Reg, J.Status
FROM
    tblJob As J
    inner join
    tblJobSession As S ON S.JobId = J.JobId
where
    S.Status = 'open'

tks. just to advise this was a stripped down query , in my main app, the query has a lot more data from other tables.
I wanted to get a base line then add in the rest bit by bit so in the end i will have all the data returned that i need.