Month Year as an ID

Steel Contributor

 

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

 

Tony2021_0-1726149967285.png

 

thank you. 

8 Replies

@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. 

 

@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.

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.

@XPS35 

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.  

 

 

Tony2021_0-1726172466263.png

 

I understand the basic principle of the question. I almost have a solution too. The problem I still have is that months without draws do not appear in a query based on tblDraws. December does not appear in such a query and you cannot show the sum of the previous month. I'm still working on that.
You can return the balance for the prior month to each transaction by calling the DateSerial function in a BETWEEN…AND operation in a correlated subquery, e.g. for a table with separate Credit and Debit columns, both of which disallow Nulls (Required property = True in Access) and have a DefaultValue property of 0:

SELECT T1.TransactionDate,T1.Credit,T1.Debit,
NZ((SELECT SUM(Credit-Debit)
FROM TransactionsCD AS T2
WHERE T2.TransactionDate BETWEEN
DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate)-1,1)
AND DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate),0)),0)
AS PriorMonthBalance
FROM TransactionsCD AS T1
ORDER BY TransactionDate;

@Ken_Sheridan 

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...

To group the transactions by TransactionDate and return the aggregated amount per day would be:

SELECT T1.TransactionDate,SUM(T1.Credit-T1.Debit) AS TotalAmount,
NZ((SELECT SUM(Credit-Debit)
FROM TransactionsCD AS T2
WHERE T2.TransactionDate BETWEEN
DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate)-1,1)
AND DATESERIAL(YEAR(T1.TransactionDate),MONTH(T1.TransactionDate),0)),0)
AS PriorMonthBalance
FROM TransactionsCD AS T1
GROUP BY TransactionDate;

Having separate Credit and Debit columns or a single Amount column in the base table is immaterial. In the latter case you'd simply SUM(Amount), assuming that the column contains positive values for credits and negative values for debits.