Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Sep 12, 2024

Month Year as an ID

 

Hi Experts,

I have been fiddling around with this query for quite some time.  I am getting nowhere with it.  What I have resorted to is trying to use MonthYearSort in a Where clause in the following OpeningBalance calculation (I typically would use an ID but I dont have one.  I dont know if this is a good idea...probably not however it is unique but just not a typical ID since its based on a date).  Not sure if some special kind of formatting would need to be used since it is based on a date.  I am grabbing a sum from another query and summing by month based on the MonthYearSort.

 

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


MonthYrSort: Year([FundingDate])*12+DatePart('m',[FundingDate])-1

 

 

thank you. 

access
  • XPS35's avatar
    XPS35
    Iron Contributor
    Sep 12, 2024

    Tony2021 I think this won't work because in the where-part of the DSum you are referring to other tables/queries than qryDrawsDecliningCumDrawn. Access can't know where to look there (which row?). 

     

    It is hard to give you a solution as we do not know anything about the tables and queries you are using. 

     

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Sep 12, 2024

      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.

      • XPS35's avatar
        XPS35
        Iron Contributor
        Sep 12, 2024
        Sorry, I don't understand what you are trying to do. Indeed, the queries are long. But most of all, I do not understand the logic. For example, the grouping of both queries is different. So I do not understand how you can compare a month to the pervious month.

        Please explain in plain words what you want to achieve.

Resources