Status Query Check

[iuianj07]

Hello guys,

I have a Job Tracking database wherein a record (a loan/work we do) in the database goes through a different phases/status to track status of the record (Loan received, In Process, Ready for Review, Sent to Client etc)


For a quick background here is my table structure (that relates to my question):

tblJobTracking:

SitusID -- PK
WeekNumber
DealName
AssetType
PropertyCount
Etc (other fields)

tblDealStatus

DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst -- user who is assigned for the record/deal
StatusChangeID -- FK to tblStatusChange
StatusDate -- Date Format is : m/d/yy h:nn AM/PM;@
StatusHours -- how many hours did the user worked on that specific status

tblStatusChange

StatusChangeID -- PK
Status -- different status/phase (Unassigned, Received, In Process etc)

tblStatusChange:

StatusChangeID Status
1 Unassigned (also when we received the deal)
2 In Process
3 Ready for Initial Review
4 Initial Review
5 Sent Back for Corrections
6 Ready for Final Review
7 Final Review
8 Sent back to Client
9 On Hold
10 Dropped


We are trying to create a query wherein it will return the SitusID if that SitusID's MaxDate's Status is still "In Process" (meaning that's the current status of the SitusID) after 3 hours

For me to get the MaxDate's status, I have a couple of queries:

qryMaxStatusDate:

SELECT tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName, Max(tblDealStatus.StatusDate) AS MaxOfStatusDate
FROM tblStatusChange INNER JOIN (tblJobTracking INNER JOIN tblDealStatus ON tblJobTracking.SitusID = tblDealStatus.SitusID) ON tblStatusChange.StatusChangeID = tblDealStatus.StatusChangeID
GROUP BY tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName;

qryCurrentStatus

SELECT qryMaxStatusDate.SitusID, qryMaxStatusDate.WeekNumber, qryMaxStatusDate.DealName, tblStatusChange.Status, qryMaxStatusDate.MaxOfStatusDate
FROM tblStatusChange INNER JOIN (qryMaxStatusDate INNER JOIN tblDealStatus ON (qryMaxStatusDate.SitusID = tblDealStatus.SitusID) AND (qryMaxStatusDate.MaxOfStatusDate = tblDealStatus.StatusDate)) ON tblStatusChange.StatusChangeID = tblDealStatus.StatusChangeID;

now I tried to create this query:

SELECT qryCurrentStatus.SitusID, qryCurrentStatus.WeekNumber, qryCurrentStatus.DealName, qryCurrentStatus.Status, qryCurrentStatus.MaxOfStatusDate AS A, DateAdd("h",3,[MaxOfStatusDate]) AS B, IIf(Now()>DateAdd("h",3,[MaxOfStatusDate]),"1","0") AS C
FROM qryCurrentStatus
WHERE (((qryCurrentStatus.Status)="In Process"));

wherein I just want to see 1 in Column C records that are still In Process after 3 hours of the date and time entered for the specific SitusID.

Is this the right approach? or have I missed something or logically incorrect?

Any help is greatly appreciated.