query criteria help

[iuianj07]

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:

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.

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?

 

[r937]

your problem is right here --

HAVING Max(tblDealStatus.StatusChangeID) = 6
And Max(tblDealStatus.StatusChangeID) <> 6

how can bofadem be true at the same time? ;-)

hence, no results



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[iuianj07]

hey,

thanks for your reply... I kinda figured that is a very wrong query...

I keep on playing with how I should write it, but still no luck...

can you help me out with how the query should be?

Thank you for your help

 

[r937]

how should the query be?

perhaps you could explain your requirements again



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

You wanted something like this ?

SELECT D.LoanNumber
  FROM tblComplete C INNER JOIN tblDealStatus D ON C.CompleteID = D.Complete
 WHERE D.StatusChangeID=6
 GROUP BY D.LoanNumber
HAVING SUM(IIf(C.Complete IN ('ASR','Data Tape'),1,0))=2
    OR SUM(IIf(C.Complete = 'Both'),1,0))=1

SELECT D.LoanNumber
  FROM tblComplete C INNER JOIN tblDealStatus D ON C.CompleteID = D.Complete
 WHERE D.StatusChangeID=6
 GROUP BY D.LoanNumber
HAVING SUM(IIf(C.Complete = 'ASR',1,0))=1
   AND SUM(IIf(C.Complete = 'Data Tape',1,0))=0

SELECT D.LoanNumber
  FROM tblComplete C INNER JOIN tblDealStatus D ON C.CompleteID = D.Complete
 WHERE D.StatusChangeID=6
 GROUP BY D.LoanNumber
HAVING SUM(IIf(C.Complete = 'ASR',1,0))=0
   AND SUM(IIf(C.Complete = 'Data Tape',1,0))=1

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

 

[iuianj07]

Hello PHV,

Thank you for your reply...

Although currently I am being pulled out into a different rush project, so I couldn't check this as of this moment. Just want to give you a heads up in case you were waiting for a response from me if these queries work.

I'll let you know right away when I get a chance to look at this.

Thank you,