Identify penultimate value from a tale based on final event string & date

[Anthony_Bell]

I have a table ('M Jobs') with job reference numbers ([DeptJobNumber]), Job Events ([JobEventDescription]) and Event Times ([JobEventDateTime] which holds the lifecycles of jobs identified by Events and their timestamps.

I also have a list of specific event strings, which I need to use to identify the last occurance of each of these events prior to the job being completed (the "Practically Complete" event).

For Example, a job starts it's life once it has been "ENTERED" and is being measured until it hits the "PRACTICALLY COMPLETE" event.

I need to pull the last occurance of a listed event prior to the job being completed.

In the example below, we can see that the last listed event prior to the job being "Practically Complete" (10/01/2020 16:24:59) was "Gas No Access NTI" (10/01/2020 15:55:38).

I would therefore need to pull this event and time along with the job number, start date and completion date.

I have attempted to do this by creating a summarised table but I am still not getting the correct results (see below). I have attempted to use || to define the specific strings but the query fails when trying to convert the string to a boolean.

Please, can someone help me out with this as I'm having a nightmare!!  

Summary Table

Specific Events List

Jobs & Events Table