Hello guys,
I am creating a new db project in our office, and it is kind of a job tracking database wherein we have a bunch of loan numbers, and we create ASRs and/or Data Tapes (using excel) and we just use the database to track the status/phase of each loan number, wherein if it has been assigned, in process, entry completed, in review, review completed, sent back to the client etc...
basically my table structure is:
tblDealStatus
DealStatusID -- PK
LoanNumber
Complete -- FK to tblComplete
Analyst --
StatusChangeID -- FK to tblStatusChange
StatusHours
StatusDate
StatusComments
tblComplete
CompleteID
Complete -- this is just a lookup, fields are ASR, DataTape and Both
tblStatusChange
StatusChangeID
Status (unassigned, assigned, In process, Ready for Review, In Review, Completed, Sent to Client, On Hold)
Now, one of the process is that, for one loan number, an ASR and/or Data Tape needs to be completed, and most of the time, one analyst should be the one who will complete an ASR and a DataTape (where the Complete field should be "Both", but there will be other times that for one loan number, one analyst will be completing an ASR and another analyst will be completing a Data Tape...
I am trying to create queries that will return loan numbers where:
1.) Both ASR and Data Tape for the loan Numbers are Completed (StatusChangeID = 6)
2.) a Loan Number have ASR Completed but the Data Tape is not (meaning the max status <> 6)
3.) a Loan Number have Data Tape Completed but the ASR is not
(meaning the max status <>6)
I tried to create this query for number 1:
but the problem is, it is also returning loan numbers for where either ASR or Data Tape has StatusChangeID = 6.
I also tried to create another query for number 2.
but it looks incorrect and is not returning anything...
Can anyone lead me to the right direction on how to create this query?
I am creating a new db project in our office, and it is kind of a job tracking database wherein we have a bunch of loan numbers, and we create ASRs and/or Data Tapes (using excel) and we just use the database to track the status/phase of each loan number, wherein if it has been assigned, in process, entry completed, in review, review completed, sent back to the client etc...
basically my table structure is:
tblDealStatus
DealStatusID -- PK
LoanNumber
Complete -- FK to tblComplete
Analyst --
StatusChangeID -- FK to tblStatusChange
StatusHours
StatusDate
StatusComments
tblComplete
CompleteID
Complete -- this is just a lookup, fields are ASR, DataTape and Both
tblStatusChange
StatusChangeID
Status (unassigned, assigned, In process, Ready for Review, In Review, Completed, Sent to Client, On Hold)
Now, one of the process is that, for one loan number, an ASR and/or Data Tape needs to be completed, and most of the time, one analyst should be the one who will complete an ASR and a DataTape (where the Complete field should be "Both", but there will be other times that for one loan number, one analyst will be completing an ASR and another analyst will be completing a Data Tape...
I am trying to create queries that will return loan numbers where:
1.) Both ASR and Data Tape for the loan Numbers are Completed (StatusChangeID = 6)
2.) a Loan Number have ASR Completed but the Data Tape is not (meaning the max status <> 6)
3.) a Loan Number have Data Tape Completed but the ASR is not
(meaning the max status <>6)
I tried to create this query for number 1:
Code:
SELECT DISTINCT tblDealStatus.LoanNumber
FROM tblComplete INNER JOIN tblDealStatus ON tblComplete.CompleteID = tblDealStatus.Complete
GROUP BY tblDealStatus.LoanNumber, tblComplete.Complete, tblDealStatus.StatusChangeID
HAVING (((tblComplete.Complete)="ASR" Or (tblComplete.Complete)="Data Tape") AND ((tblDealStatus.StatusChangeID)=6));
but the problem is, it is also returning loan numbers for where either ASR or Data Tape has StatusChangeID = 6.
I also tried to create another query for number 2.
Code:
SELECT DISTINCT tblDealStatus.LoanNumber
FROM tblComplete INNER JOIN tblDealStatus ON tblComplete.CompleteID = tblDealStatus.Complete
WHERE (((tblComplete.Complete)="ASR" And (tblComplete.Complete)="Data Tape"))
GROUP BY tblDealStatus.LoanNumber
HAVING (((Max(tblDealStatus.StatusChangeID))=6 And (Max(tblDealStatus.StatusChangeID))<>6));
but it looks incorrect and is not returning anything...
Can anyone lead me to the right direction on how to create this query?