Sep 12 2024 08:40 AM
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.
Sep 12 2024 10:09 AM
@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.
Sep 12 2024 10:38 AM - edited Sep 12 2024 10:39 AM
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.
Sep 12 2024 12:27 PM
Sep 12 2024 01:25 PM
Hello, thank you for the response. I have worked up a simple example. I think the attached will explain it if look at the sum formula in E18 - E22
maybe you wont need the MonthYrSort and can simply use the actual dates instead but I suspect a date will require a special formatting I dont know how to do.
Sep 13 2024 01:31 PM
Sep 13 2024 06:15 PM
Sep 14 2024 06:06 AM
Hi XPS and Ken,
thank you both for your responses.
XPS, i will wait for your response. Many thanks.
Ken, that is amazing. Could that query be grouped by TransactionDate? I have fiddled around with it by adding the grouping (the summation button) but its not grouping like I thought it would. I dont need to show the debit and credit so that might help. I will add this is not exactly what I was looking since I dont have debits and credits...just [Amount] but has given me some ideas and might be able to add efficiency by using this query and making some changes for my application (by summing only the [Amount] not summing debit and credit) but would need to group.
Your response might have been directed at XPS I am not sure. thank you sir...
Sep 14 2024 02:29 PM