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


A quick background of a process we have in the database... let's say that we received a loan from a Client, so the first status that we put into the database is Unassigned (StatusChangeID = 1) then somewhere along the way we've decided that we can't complete the loan therefore we have to put it On Hold, we create a status of On Hold (StatusChangeID = 9). Then let's say after a day or two the issue has been resolved and can be worked on again, what we do is we create another Unassigned Record (StatusChangeID = 1) with the date when that On hold issue has been Resolved. So for a summary, if a loan has been put On Hold and has been resolved, there will always be two Unassigned Status child records in the database.


We have a weekly/monthly report query that the Project Coordinator runs to track how many loans we have received that is grouped by Asset Type... the query is below:


qryTotalAssetsRcvd


SELECT tblAssetType.AssetType, Count(tblJobTracking.PropertyCount) AS Sizings, Sum(IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount])) AS Properties
FROM (tblAssetType INNER JOIN (tblJobTracking INNER JOIN tblDealStatus ON tblJobTracking.SitusID = tblDealStatus.SitusID) ON tblAssetType.AssetID = tblJobTracking.AssetType) INNER JOIN qryMinUnassignedDate ON tblJobTracking.SitusID = qryMinUnassignedDate.SitusID
WHERE (((tblDealStatus.StatusChangeID)=1) AND ((qryMinUnassignedDate.MinOfStatusDate) Between [Enter Start Date] And [Enter End Date]))


GROUP BY tblAssetType.AssetType;


where qryMinUnassignedDate's code is:


SELECT tblDealStatus.SitusID, Min(tblDealStatus.StatusDate) AS MinOfStatusDate, tblDealStatus.StatusChangeID
FROM tblDealStatus GROUP BY tblDealStatus.SitusID, tblDealStatus.StatusChangeID
HAVING (((tblDealStatus.StatusChangeID)=1));



The reason I created qryMinUnassigned Date is to capture the Minimum Unassigned status of each loan in case there are two Unassigned records on a loan (because it was put On Hold)

Whenever I run qryTotalAssetsRcvd it gives the wrong number because it counts and sums the Property Count twice for loans that have 2 Unassigned records:

example if I pull down tblJobTracking.SitusID into qryTotalAssetsRcvd, it will return this result:

AssetType Sizings Properties SitusID
Hotel 2 34 1989


But if I look at my tblJobTracking with SitusID = 1989, the Property Count is 17. Therefore, the correct number for this SitusID should be:

AssetType Sizings Properties SitusID
Hotel 1 17 1989


This messes up the report by having duplicate numbers which makes the report inaccurate.

Can someone please help me out to correct this issue? I will be forever grateful...

Please let me know If I need to explain further and if you have any questions.

Thank you very much!