"Self" Join Query + Another Table?

[NicholasP]

So I have the SQL code below which helps me determine the change in DACE....now I want to incorporate another table "Loans"....where I want to figure out how to sum all loans that have a "C" or a "3" in the field "Status History" for a given month

I have a query that does what I want, but I am having a hard time incorporating that query into the SQL code below...

Here is the original SQL code I want to add to:

SELECT a.SECURITY_NAME, a.Mnth, (a.DACE-b.DACE) AS DACENetChange, a.DACE, 
FROM Securities AS b, Securities AS a
WHERE (((a.SECURITY_NAME)=b.SECURITY_NAME) And ((a.Mnth)=#11/1/2011#) And ((DateSerial(Year(a.Mnth),Month(a.Mnth)-1,1))=b.Mnth));

Here is the SQL query I want to incorporate somehow in the above SQL code:

SELECT Securities.SECURITY_NAME, Sum(Loans.[Curr Trust Bal(USD)]) AS [SumOfCurr Trust Bal(USD)]
FROM Securities INNER JOIN Loans ON Securities.Master=Loans.Master
WHERE ((Left(Loans![Status History],1)="C" Or Left(Loans![Status History],1)="3") And ((Securities.Mnth)=#11/1/2011#))
GROUP BY Securities.SECURITY_NAME;

Any help would be greatly appreciated as I'm not very good at Access.

 

[NicholasP]

I'm really stumped on this one and maybe I haven't given enough info? As such I've attached a small version of the file to this post. If anyone has an idea on how to accomplish what I'm looking for, I'd greatly appreciate it.

Thanks
Nick

 

[dnlbrky]

I'm a little confused about what you're trying to accomplish, but does the SQL below give you the expected results?

SELECT Securities.SECURITY_NAME, Sum(Loans.[Curr Trust Bal(USD)]) AS [SumOfCurr Trust Bal(USD)], Query1.DACENetChange, Query1.WALNetChange, Query1.Account, Query1.Holdings, Query1.MTG_WAL, Query1.DEF_ADJ_CREDIT_SUPPORT, Query1.PX_LAST, Query1.PXNetChange
FROM (Securities INNER JOIN Loans ON Securities.Master = Loans.Master) LEFT JOIN Query1 ON (Securities.Mnth = Query1.Mnth) AND (Securities.SECURITY_NAME = Query1.SECURITY_NAME)
WHERE (((Loans.[Status History]) Like "*C*" Or (Loans.[Status History]) Like "*3*") AND ((Securities.Mnth)=#11/1/2011#))
GROUP BY Securities.SECURITY_NAME, Query1.DACENetChange, Query1.WALNetChange, Query1.Account, Query1.Holdings, Query1.MTG_WAL, Query1.DEF_ADJ_CREDIT_SUPPORT, Query1.PX_LAST, Query1.PXNetChange;

 

[NicholasP]

I'm a little confused about what you're trying to accomplish, but does the SQL below give you the expected results?

Thanks for your response, I would've got back to you sooner, but I was traveling...

Your SQL is pretty close, but I only want to sum the values where the first value of "Status History" = 3 or C (hence why I've tried to use the LEFT function), not sum the values where any of "Status History" has ever been 3 or C....trying to figure that out now (though if this seems obvious to you, please feel free to help, I'm still not quite getting it)...

My next step is to calculate the differential from the preceding month...not sure about that yet, but I will try...