Month Year as an ID

[Tony2021]

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. 

[#2]

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

[#3]

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

[#4]

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.

[#5]

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

[#6]

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.

[#7]

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;

[#8]

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

[#9]

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.