Query help to substitute Excel Vlookup 1

[iuianj07]

Hello guys,

I need some help here... we are running into a manual phase in a database... which I am hoping that there is a shorter way to do it...

Basically, I have a query below:

SELECT tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], "2010Q2" AS TestPeriod, tblAbstract.Watchlist
FROM tblAbstract WHERE (((tblAbstract.DateReviewed) Is Not Null) AND ((tblAbstract.[Special Servicing])=0) AND ((tblAbstract.PaidOff)=0) AND ((tblAbstract.[Not in Scope])=0) AND ((tblAbstract.InactiveChk)=0) AND ((tblAbstract.Defeased)=0))
GROUP BY tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], "2010Q2", tblAbstract.Watchlist, tblAbstract.TestsPerYear
HAVING (((tblAbstract.[East/West])="East") AND ((tblAbstract.TestsPerYear)="Quarterly" Or (tblAbstract.TestsPerYear)="Monthly"))
ORDER BY tblAbstract.TestsPerYear;

Then the result of this query, will be exported into Excel... after that, we have a table named WorkFlow, which contains the whole pool of records, then on the WorkFlow table, I just filter it to East/West = "East" and TestPeriod = "2010Q2"... then the loans filtered by these criteria will then just have the LoanNumber column be exported into the same excel file from the query above...

Then after that, I do a vlookup to the list of Loans from the WorkFlow table, that basically will filter down all loans that is in the query and is NOT in the WorkFlow table...

Long story short.. it should only have loans that are in the tblAbstract but is NOT in the WorkFlow table...

I tried to do create a query to give this result...but I think is incorrect...

SELECT WorkFlow.LoanNumber, tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], WorkFlow.TestPeriod, tblAbstract.Watchlist
FROM tblAbstract LEFT JOIN WorkFlow ON tblAbstract.LoanNumber = WorkFlow.LoanNumber
WHERE (((WorkFlow.LoanNumber) Is Null) AND ((tblAbstract.[East/West])="East") AND ((WorkFlow.TestPeriod)="2010Q2") AND ((tblAbstract.TestsPerYear)="Quarterly" Or (tblAbstract.TestsPerYear)="Monthly" Or (tblAbstract.TestsPerYear)="Semi-Annual") AND ((tblAbstract.DateReviewed) Is Not Null) AND ((tblAbstract.[Special Servicing])=0) AND ((tblAbstract.PaidOff)=0) AND ((tblAbstract.[Not in Scope])=0) AND ((tblAbstract.InactiveChk)=0) AND ((tblAbstract.Defeased)=0))
ORDER BY tblAbstract.TestsPerYear;

Is there any way you could help me out with this?

Please let me know if you have any questions or if I need to provide more information.

Any help is greatly appreciated...

Thank you

 

[PHV]

AND ((WorkFlow.TestPeriod)="2010Q2")
The above criteria defeats the LEFT join ...

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

 

[iuianj07]

Hello PHV...

I was able to research, and found out a solution for my problem above...

SELECT tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], [Enter] AS TestPeriod, tblAbstract.Watchlist
FROM tblAbstract
WHERE (((tblAbstract.[East/West])=[Enter East or West]) AND ((tblAbstract.TestsPerYear)="Quarterly" Or (tblAbstract.TestsPerYear)="Monthly" Or (tblAbstract.TestsPerYear)="Semi-Annually") AND ((tblAbstract.DateReviewed) Is Not Null) AND ((tblAbstract.[Special Servicing])=0) AND ((tblAbstract.PaidOff)=0) AND ((tblAbstract.[Not in Scope])=0) AND ((tblAbstract.InactiveChk)=0) AND ((tblAbstract.Defeased)=0) AND ((Exists (SELECT WorkFlow.LoanNumber FROM WorkFlow WHERE ((WorkFlow.TestPeriod)= [Enter TestPeriod]) AND (tblAbstract.LoanNumber = WorkFlow.LoanNumber)))=False))
ORDER BY tblAbstract.TestsPerYear;

In case you are interested and if you might see anything that I could add...

Thanks again

 

[iuianj07]

Hello, I have a follow up question...

So I have this query:

SELECT tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], "2010Q1" AS TestPeriod, tblAbstract.Watchlist
FROM tblAbstract
WHERE (((tblAbstract.[East/West])=[Enter East or West]) AND ((tblAbstract.TestsPerYear)="Quarterly" Or (tblAbstract.TestsPerYear)="Monthly") AND ((tblAbstract.DateReviewed) Is Not Null) AND ((tblAbstract.[Special Servicing])=0) AND ((tblAbstract.PaidOff)=0) AND ((tblAbstract.[Not in Scope])=0) AND ((tblAbstract.InactiveChk)=0) AND ((tblAbstract.Defeased)=0) AND ((Exists (SELECT WorkFlow.LoanNumber FROM WorkFlow WHERE ((WorkFlow.TestPeriod)= "2010Q1") AND (tblAbstract.LoanNumber = WorkFlow.LoanNumber)))=False))
ORDER BY tblAbstract.TestsPerYear;

my problem though is that I have another table named tblExcludeLoanNumbers which contains loan numbers that should always be excluded when I run the query above... how could I modify my query above so that it wouldn't include loan numbers from tblExcludeLoanNumbers?

I tried to add another Exists function, but it gives an error

Any help is greatly appreciated.

thanks

 

[PHV]

One way:
SELECT ...
FROM ...
WHERE ...
AND tblAbstract.LoanNumber NOT IN (SELECT LoanNumber FROM tblExcludeLoanNumbers)
ORDER ...

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

 

[iuianj07]

hello PHV,

I added it to the query, but it gives a syntax error... can you please look at what part is incorrect?

Thank you

SELECT tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], "2010Q1" AS TestPeriod, tblAbstract.Watchlist
FROM tblAbstract
WHERE (((tblAbstract.[East/West])=[Enter East or West]) AND ((tblAbstract.TestsPerYear)="Quarterly" Or (tblAbstract.TestsPerYear)="Monthly") AND ((tblAbstract.DateReviewed) Is Not Null) AND ((tblAbstract.LoanNumber NOT IN (SELECT LoanNumber FROM tblExcludeLoanNumbers))
 ((tblAbstract.[Special Servicing])=0) AND ((tblAbstract.PaidOff)=0) AND ((tblAbstract.[Not in Scope])=0) AND ((tblAbstract.InactiveChk)=0) AND ((tblAbstract.Defeased)=0) AND ((Exists (SELECT WorkFlow.LoanNumber FROM WorkFlow WHERE ((WorkFlow.TestPeriod)= "2010Q1") AND (tblAbstract.LoanNumber = WorkFlow.LoanNumber)))=False))
ORDER BY tblAbstract.TestsPerYear;

 

[PHV]

SELECT PropertyID, InvestorNumber, LoanNumber, [East/West], '2010Q1' AS TestPeriod, Watchlist
FROM tblAbstract
WHERE [East/West]=[Enter East or West]
AND TestsPerYear IN ('Quarterly','Monthly')
AND DateReviewed Is Not Null
AND LoanNumber NOT IN (SELECT LoanNumber FROM tblExcludeLoanNumbers)
AND [Special Servicing]=0
AND PaidOff=0
AND [Not in Scope]=0
AND InactiveChk=0
AND Defeased=0
AND LoanNumber NOT IN (SELECT LoanNumber FROM WorkFlow WHERE TestPeriod='2010Q1')
ORDER BY TestsPerYear

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

 

[iuianj07]

okay, I tried to re-write the SQL, it doesn't give the syntax error anymore,,

SELECT tblAbstract.PropertyID, tblAbstract.InvestorNumber, tblAbstract.LoanNumber, tblAbstract.[East/West], "2010Q2" AS TestPeriod, tblAbstract.Watchlist, tblAbstract.LoanNumber
FROM tblAbstract
WHERE (((tblAbstract.[East/West])=[Enter East or West]) AND ((tblAbstract.TestsPerYear)="Quarterly" Or (tblAbstract.TestsPerYear)="Monthly" Or (tblAbstract.TestsPerYear)="Semi-Annually") AND ((tblAbstract.DateReviewed) Is Not Null) AND ((tblAbstract.[Special Servicing])=0) AND ((tblAbstract.PaidOff)=0) AND ((tblAbstract.[Not in Scope])=0) AND ((tblAbstract.InactiveChk)=0) AND ((tblAbstract.Defeased)=0) AND ((Exists (SELECT WorkFlow.LoanNumber FROM WorkFlow WHERE ((WorkFlow.TestPeriod)= "2010Q2") AND (tblAbstract.LoanNumber = WorkFlow.LoanNumber)))=False) AND ((tblAbstract.LoanNumber) Not In (Select LoanNumber FROM ExcludeLoanNumbers)))
ORDER BY tblAbstract.TestsPerYear;

 

[iuianj07]

thanks PHV, our posts went in around the same time... thanks again

 

[PHV]

And if you prefer the outer join way:
SELECT A.PropertyID, A.InvestorNumber, A.LoanNumber, A.[East/West], '2010Q2' AS TestPeriod, A.Watchlist
FROM (tblAbstract A
LEFT JOIN ExcludeLoanNumbers X ON A.LoanNumber=X.LoanNumber)
LEFT JOIN (SELECT LoanNumber FROM WorkFlow WHERE TestPeriod='2010Q2'
) W ON A.LoanNumber=W.LoanNumber
WHERE X.LoanNumber IS NULL AND W.LoanNumber IS NULL
AND A.[East/West]=[Enter East or West]
AND A.TestsPerYear IN ('Quarterly','Monthly','Semi-Annually')
AND A.DateReviewed Is Not Null
AND A.[Special Servicing]=0
AND A.PaidOff=0
AND A.[Not in Scope]=0
AND A.InactiveChk=0
AND A.Defeased=0
ORDER BY A.TestsPerYear

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