Month Year as an ID

XPS35 

HI XPS, 

thanks for the response.  I have provided both sql's below (Q1 and the referenced one qryDrawsDecliningCumDrawn)  

I think I did make a mistake though:

here is the revised in qryDrawsDecliningCumDrawn:

OpeningBal: format(Nz(DSum("SumOfAmount","qryDrawsDecliningCumDrawn","Type=" & [tblDraws].[Type] & " And [MonthYearSort] < " & Nz([MonthYearSort],0)),0),"Currency")

qryDrawsDecliningCumDrawn

SELECT

tblDrawsDetails.DrawsIDfk, Sum(tblDrawsDetails.Amount) AS SumOfAmount, tblDrawTypeDropBox.Type, tblFacility.ProjID, tblDrawTypeDropBox.ID, tblFacility.TypeIDfk, tblDrawsDetails.FacIDfk, First(tblDrawsDetails.IDDetailpk) AS FirstOfIDDetailpk, tblDraws.FundingDate, Year([FundingDate])*12+DatePart('m',[FundingDate])-1 AS MonthYrSort
FROM

tblDraws INNER JOIN ((tblDrawsDetails LEFT JOIN tblFacility ON tblDrawsDetails.FacIDfk = tblFacility.ID) LEFT JOIN tblDrawTypeDropBox ON tblFacility.TypeIDfk = tblDrawTypeDropBox.ID) ON tblDraws.ID = tblDrawsDetails.DrawsIDfk
GROUP BY

tblDrawsDetails.DrawsIDfk, tblDrawTypeDropBox.Type, tblFacility.ProjID, tblDrawTypeDropBox.ID, tblFacility.TypeIDfk, tblDrawsDetails.FacIDfk, tblDraws.FundingDate, Year([FundingDate])*12+DatePart('m',[FundingDate])-1, tblDraws.Type
HAVING (((tblDraws.Type)<>4));

here is the query I am putting the Sum in:

Note that the sum I am looking for is for the PREVIOUS month. 

Q1:

SELECT

First(tblDraws.ID) AS FirstOfID1, Year([FundingDate])*12+DatePart('m',[FundingDate])-1 AS MonthYrSort, Format([FundingDate],"yyyy"", ""mmmm") AS FundingDateGrp, tblDrawTypeDropBox.Type, tblFacility.Commitment, Sum(qryDrawsDecliningCumDrawn2.SumOfAmount) AS SumDDIDC, Format(Nz(DSum("SumOfAmount","qryDrawsDecliningCumDrawn","Type=" & [tblDraws].[Type] & " And [MonthYrSort] < " & Nz([MonthYrSort],0)),0),"Currency") AS OpeningBal
FROM

qryDrawsDecliningCumDrawn2 INNER JOIN (((tblInterest INNER JOIN tblDraws ON tblInterest.DrawIDfk = tblDraws.ID) LEFT JOIN tblDrawTypeDropBox ON tblDraws.Type = tblDrawTypeDropBox.ID) LEFT JOIN tblFacility ON tblDraws.Type = tblFacility.ID) ON qryDrawsDecliningCumDrawn2.FirstOfID = tblDraws.ID
GROUP BY Year([FundingDate])*12+DatePart('m',[FundingDate])-1, Format([FundingDate],"yyyy"", ""mmmm"), tblDrawTypeDropBox.Type, tblFacility.Commitment, tblDrawTypeDropBox.ID, Format(Nz(DSum("SumOfAmount","qryDrawsDecliningCumDrawn","Type=" & [tblDraws].[Type] & " And [MonthYrSort] < " & Nz([MonthYrSort],0)),0),"Currency")
HAVING (((tblDrawTypeDropBox.ID)=1));

Just as a summary.  I need the monthly sum of SumOfAmount as found in qryDrawsDecliningCumDrawn and put it in Q1 but note its for the previous month

sorry for the sql's.  i know they are long.  

thank you very much.