Query/Report as of prior Saturday Help

[iuianj07]

Hello guys,

I have a Job Tracking database wherein our team does sizing reports (in case you're familiar with Real Estate terms) from our client and we track the status of each loan in this database.

I want to share the table structure of the Job Tracking database (tables that I think relates to my question):

tblJobTracking

SitusID -- PK
WeekNumber
DealName
ClientOriginator -- FK to tblClientOriginator
Broker
City
State -- FK to tblSTate
Region -- FK to tblRegions
AssetType -- FK to tblAssetType
PropertyCount
Priority
PCComments
DocumentLinks
Update
Model -- FK to tblModel
ConsolidatedFS -- Yes/No field
ConsolidatedRR -- Yes/No field

tblDealStatus

DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst
StatusChangeID -- FK to tblStatusChange
StatusDate -- Date/Time format m/d/yy h:nn AM/PM;@
StatusHours
StatusComments


tblStatusChange -- PK
Status --
SortOrder

tblStatusChange -- Different statuses

StatusChangeID Status
1 Unassigned
2 In Process
3 Ready for Initial Review
4 Initial Review
5 Sent back - Corrections
6 Ready for Final Review
7 Final Review
8 Sent
9 On Hold
10 Dropped
11 Assigned
12 Unassigned - Resolved Issue


Okay, so the above is the table structure. Now our Project Coordinator sends a Weekly Report every Monday to the Project Managers and VPs about the current status of loans we received, are in process, on hold or sent from the prior week (our week ending day is Saturday) so basically the PC will submit the current status of loans as of Saturday.

I have created a query/report for this but it is not returning the results we really need. What it does is, it gives the current status of each loan as of Monday (the time that the query was ran) instead of the current status of loan as of Saturday...

I am not good at optimized or simplified queries, I always tend to create a lot of subqueries. but below is what I created for this specific report.

qryMaxStatusDate-InProcessForWeeklyReport

SELECT tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName, Max(tblDealStatus.StatusDate) AS MaxOfStatusDate, tblJobTracking.UBSOriginator
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, tblJobTracking.UBSOriginator
HAVING (((tblJobTracking.SitusID) Not In (SELECT Distinct tblDealStatus.SitusID FROM tblDealStatus WHERE (((tblDealStatus.[StatusChangeID])=8 Or (tblDealStatus.[StatusChangeID])=9 Or (tblDealStatus.[StatusChangeID])=10)); )));

qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt

SELECT tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName, Max(tblDealStatus.StatusDate) AS MaxOfStatusDate, tblJobTracking.UBSOriginator
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, tblJobTracking.UBSOriginator
HAVING (((tblJobTracking.SitusID) In (SELECT Distinct tblDealStatus.SitusID FROM tblDealStatus WHERE (((tblDealStatus.[StatusChangeID])=8 Or (tblDealStatus.[StatusChangeID])=9 Or (tblDealStatus.[StatusChangeID])=10)); )));

then using the above queris, I used those as sub queries to these:

qryCurrentStatus-InProcessFORWeeklyRpt

SELECT [qryMaxStatusDate-InProcessForWeeklyRpt].SitusID, [qryMaxStatusDate-InProcessForWeeklyRpt].WeekNumber, [qryMaxStatusDate-InProcessForWeeklyRpt].DealName, [qryMaxStatusDate-InProcessForWeeklyRpt].UBSOriginator, tblStatusChange.Status, [qryMaxStatusDate-InProcessForWeeklyRpt].MaxOfStatusDate, tblDealStatus.Analyst
FROM [qryMaxStatusDate-InProcessForWeeklyRpt] INNER JOIN (tblStatusChange INNER JOIN tblDealStatus ON tblStatusChange.StatusChangeID = tblDealStatus.StatusChangeID) ON ([qryMaxStatusDate-InProcessForWeeklyRpt].SitusID = tblDealStatus.SitusID) AND ([qryMaxStatusDate-InProcessForWeeklyRpt].MaxOfStatusDate = tblDealStatus.StatusDate);

qryCurrentStatus-SentDroppedOHWeeklyRpt

SELECT [qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].SitusID, [qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].WeekNumber, [qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].DealName, [qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].UBSOriginator, tblStatusChange.Status, [qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].MaxOfStatusDate, tblDealStatus.Analyst
FROM [qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt] INNER JOIN (tblStatusChange INNER JOIN tblDealStatus ON tblStatusChange.StatusChangeID = tblDealStatus.StatusChangeID) ON ([qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].SitusID = tblDealStatus.SitusID) AND ([qryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].MaxOfStatusDate = tblDealStatus.StatusDate);

then finally, I use a union query:

uqryCurrentStatusRprt_Weekly

SELECT [qryCurrentStatus-InProcessFORWeeklyRpt].Status, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM tblJobTracking INNER JOIN [qryCurrentStatus-InProcessFORWeeklyRpt] ON tblJobTracking.SitusID = [qryCurrentStatus-InProcessFORWeeklyRpt].SitusID
WHERE ((([qryCurrentStatus-InProcessFORWeeklyRpt].MaxOfStatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [qryCurrentStatus-InProcessFORWeeklyRpt].Status;
UNION ALL SELECT [qryCurrentStatus-SentDroppedOHWeeklyRpt].Status, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM tblJobTracking INNER JOIN [qryCurrentStatus-SentDroppedOHWeeklyRpt] ON tblJobTracking.SitusID = [qryCurrentStatus-SentDroppedOHWeeklyRpt].SitusID
WHERE ((([qryCurrentStatus-SentDroppedOHWeeklyRpt].MaxOfStatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [qryCurrentStatus-SentDroppedOHWeeklyRpt].Status;

Yes I know it was a pain and such a long way approach to create this report, but it doesn't give the results that I wanted though... this query gives the Current Status of the loans as of the day this query was ran (which is normally Monday) wherein what we really want is the Current Status of the loan as of Saturday...

An example why the result will be different between Friday/Saturday and Monday...

For example, we received loan 1111 Friday, and it is currently In process... but come Monday morning it has been completed and sent to client... so when the PC runs the query Monday afternoon... loan 1111's current status is Sent to Client instead of In Process which is not what the Project Managers want to see... I hope I made sense...

Any help is greatly appreciated, currently the PC is manually counting/checking the loans for this specific report, and I am hoping I could fix this problem.. I would think the problem is because I use the Max date function for status date, but couldn't figure out another way to just get the result I wanted.

Thank you very much again... Please let me know if I need to add more information

 

[PHV]

A starting point:

SELECT JT.SitusID, JT.WeekNumber, JT.DealName, Max(DS.StatusDate) AS MaxOfStatusDate, JT.UBSOriginator
FROM tblJobTracking JT INNER JOIN tblDealStatus DS ON JT.SitusID = DS.SitusID
WHERE JT.SitusID Not In (SELECT SitusID FROM tblDealStatus WHERE StatusChangeID IN (8,9,10) AND StatusDate<=(Date()-Weekday(Date())))
AND DS.StatusDate <= (Date()-Weekday(Date()))
GROUP BY JT.SitusID, JT.WeekNumber, JT.DealName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Thanks PHV for your reply,

I created these queries:

testqryMaxStatusDate-InProcessForWeeklyReport

SELECT JT.SitusID, JT.WeekNumber, JT.DealName, Max(DS.StatusDate) AS MaxOfStatusDate, JT.UBSOriginator
FROM tblJobTracking AS JT INNER JOIN tblDealStatus AS DS ON JT.SitusID = DS.SitusID
WHERE (((JT.SitusID) Not In (SELECT SitusID FROM tblDealStatus WHERE StatusChangeID IN (8,9,10) AND StatusDate<=(Date()-Weekday(Date())))) AND ((DS.StatusDate)<=(Date()-Weekday(Date()))))
GROUP BY JT.SitusID, JT.WeekNumber, JT.DealName, JT.UBSOriginator;

testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt

SELECT JT.SitusID, JT.WeekNumber, JT.DealName, Max(DS.StatusDate) AS MaxOfStatusDate, JT.UBSOriginator
FROM tblJobTracking AS JT INNER JOIN tblDealStatus AS DS ON JT.SitusID = DS.SitusID
WHERE (((JT.SitusID) In (SELECT SitusID FROM tblDealStatus WHERE StatusChangeID IN (8,9,10) AND StatusDate<=(Date()-Weekday(Date())))) AND ((DS.StatusDate)<=(Date()-Weekday(Date()))))
GROUP BY JT.SitusID, JT.WeekNumber, JT.DealName, JT.UBSOriginator;

then

testqryCurrentStatus-InProcessFORWeeklyRpt

SELECT [testqryMaxStatusDate-InProcessForWeeklyReport].SitusID, [testqryMaxStatusDate-InProcessForWeeklyReport].WeekNumber, [testqryMaxStatusDate-InProcessForWeeklyReport].DealName, [testqryMaxStatusDate-InProcessForWeeklyReport].UBSOriginator, tblStatusChange.Status, [testqryMaxStatusDate-InProcessForWeeklyReport].MaxOfStatusDate, tblDealStatus.Analyst
FROM (tblDealStatus INNER JOIN tblStatusChange ON tblDealStatus.StatusChangeID = tblStatusChange.StatusChangeID) INNER JOIN [testqryMaxStatusDate-InProcessForWeeklyReport] ON (tblDealStatus.SitusID = [testqryMaxStatusDate-InProcessForWeeklyReport].SitusID) AND (tblDealStatus.StatusDate = [testqryMaxStatusDate-InProcessForWeeklyReport].MaxOfStatusDate);

testqryCurrentStatus-SentDroppedOHWeeklyRpt

SELECT [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].SitusID, [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].WeekNumber, [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].DealName, [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].UBSOriginator, tblStatusChange.Status, [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].MaxOfStatusDate, tblDealStatus.Analyst
FROM (tblDealStatus INNER JOIN tblStatusChange ON tblDealStatus.StatusChangeID = tblStatusChange.StatusChangeID) INNER JOIN [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt] ON (tblDealStatus.SitusID = [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].SitusID) AND (tblDealStatus.StatusDate = [testqryMaxStatusDate-SentDroppedOnHoldForWeeklyRpt].MaxOfStatusDate);

then

qryInProcessCount

SELECT [testqryCurrentStatus-InProcessFORWeeklyRpt].Status, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM tblJobTracking INNER JOIN [testqryCurrentStatus-InProcessFORWeeklyRpt] ON tblJobTracking.SitusID = [testqryCurrentStatus-InProcessFORWeeklyRpt].SitusID
WHERE ((([testqryCurrentStatus-InProcessFORWeeklyRpt].MaxOfStatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [testqryCurrentStatus-InProcessFORWeeklyRpt].Status;

qryOnHoldDroppedCount
[/code]
SELECT [testqryCurrentStatus-SentDroppedOHWeeklyRpt].Status, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM tblJobTracking INNER JOIN [testqryCurrentStatus-SentDroppedOHWeeklyRpt] ON tblJobTracking.SitusID = [testqryCurrentStatus-SentDroppedOHWeeklyRpt].SitusID
WHERE ((([testqryCurrentStatus-SentDroppedOHWeeklyRpt].MaxOfStatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [testqryCurrentStatus-SentDroppedOHWeeklyRpt].Status;
[/code]


then my union query

SELECT [testqryCurrentStatus-InProcessFORWeeklyRpt].Status, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM tblJobTracking INNER JOIN [testqryCurrentStatus-InProcessFORWeeklyRpt] ON tblJobTracking.SitusID = [testqryCurrentStatus-InProcessFORWeeklyRpt].SitusID
WHERE ((([testqryCurrentStatus-InProcessFORWeeklyRpt].MaxOfStatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [testqryCurrentStatus-InProcessFORWeeklyRpt].Status;
UNION ALL SELECT [testqryCurrentStatus-SentDroppedOHWeeklyRpt].Status, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM tblJobTracking INNER JOIN [testqryCurrentStatus-SentDroppedOHWeeklyRpt] ON tblJobTracking.SitusID = [testqryCurrentStatus-SentDroppedOHWeeklyRpt].SitusID
WHERE ((([testqryCurrentStatus-SentDroppedOHWeeklyRpt].MaxOfStatusDate) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [testqryCurrentStatus-SentDroppedOHWeeklyRpt].Status;

although I think it still doesn't give the right result...

Have I missed anything?

Thank you again