Trouble with running totals and fiscal year

[Sketchin]

I have a table full of projects that has a field for the amount of funding that each project has committed to it. I am trying to create a query that will show a running total of committed funding, grouped by fiscal year. I have already figured out how to calculate the fiscal year using this equation:

FYear: Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1

I sort of have the running total working except that the last 2 fiscal years yields the same sum. The equation for the running total is here:

RunTotTECTERRACommitment: Format(DSum("[TECTERRACommitment]","tblProject","Datepart('yyyy',[DateContractSigned])<=" & [Fyear] & ""),"$0,000.00")

The running total works correctly if I remove the "+1" off the end of the equation, but then the fiscal year field displays incorrectly.
FYear RunTotTECTERRACommitment RunTotBudgetedCost IndustryContribution

Below is the example of the query result. The value for the last 2 years should not be the same.

2011 $8,295,357.00 $15,752,522.00 $7,457,165.00
2012 $15,980,337.00 $28,968,619.00 $12,988,282.00
2013 $20,620,203.00 $37,351,847.00 $16,731,644.00
2014 $23,180,663.00 $42,412,307.00 $19,231,644.00
2015 $23,180,663.00 $42,412,307.00 $19,231,644.00

 

[vbaInet]

Let's see the full SQL.

 

[Sketchin]

So the first query is this:

SELECT tblProject.ProjectID, Sum(tblProject.TECTERRACommitment)
AS SumOfTECTERRACommitment, tblLookup_ProgramType.ProgramType, Sum(tblProject.BudgetedCost)
AS SumOfBudgetedCost, Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)
AS FYear, Format(DSum("[TECTERRACommitment]","tblProject","Datepart('yyyy',[DateContractSigned])<=" & [Fyear] & ""),"$0,000.00") AS RunTotTECTERRACommitment, Format(DSum("[BudgetedCost]","tblProject","Datepart('yyyy',[DateContractSigned])<=" & [Fyear] & ""),"$0,000.00")
AS RunTotBudgetedCost, Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 AS FYearExport
FROM tblLookup_ProgramType
RIGHT JOIN tblProject
ON tblLookup_ProgramType.ProgramTypeID = tblProject.ProgramTypeID
GROUP BY tblProject.ProjectID, tblLookup_ProgramType.ProgramType, Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0), Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.DateContractSigned, tblProject.ProjID
HAVING (((tblProject.DateContractSigned) Is Not Null));


The 2nd query is this:

SELECT qryDashboardChart1.FYearExport, qryDashboardChart1.RunTotTECTERRACommitment, qryDashboardChart1.RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
AS IndustryContribution
FROM qryDashboardChart1
GROUP BY qryDashboardChart1.FYearExport, qryDashboardChart1.RunTotTECTERRACommitment, qryDashboardChart1.RunTotBudgetedCost;

The first query is listing all records where the second query is grouping and summing by fiscal year, to be exported to an excel spreadsheet.

 

[Sketchin]

Sorry, I should mention that since my first post, I have a duct tape solution where I have a FYear field that is used in the running sum, and an FYearExport field that is simply Fyear+1 to be used for exporting to Excel.

 

[vbaInet]

It would be nice to have SQL in code tags for next time.

1. You need to perform the running sum in a query that's based on the first. Basically if you want to perform calculations on a derived field (such as Count, Sum etc), don't do it in the query where the aggregate is being calculated. Do it in the query where the Sum has already been worked out.
2. Use the Year() function instead of DatePart(). It's shorter.

 

[MarkK]

I would do running totals in the final output, so if you are doing a report, do it there. Handle the format event for every report section and do the math. If you are putting data into a listview, do your running total there.

The thing about a running total is you have to do explicit math on every line, and that is slow, but you have to work with every line when you do your output. Makes sense to do running total--and sequences--then.

 

[Sketchin]

It would be nice to have SQL in code tags for next time.

1. You need to perform the running sum in a query that's based on the first. Basically if you want to perform calculations on a derived field (such as Count, Sum etc), don't do it in the query where the aggregate is being calculated. Do it in the query where the Sum has already been worked out.
2. Use the Year() function instead of DatePart(). It's shorter.

Sorry about that, I knew there was a certain way to present the string, but couldn't remember what it was!

I will try out your suggestions and get back to you with any further questions.

 

[vbaInet]

Sorry about that, I knew there was a certain way to present the string, but couldn't remember what it was!

No problem. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[Sketchin]

I would do running totals in the final output, so if you are doing a report, do it there. Handle the format event for every report section and do the math. If you are putting data into a listview, do your running total there.

The thing about a running total is you have to do explicit math on every line, and that is slow, but you have to work with every line when you do your output. Makes sense to do running total--and sequences--then.

I agree with you if I was outputting this data to a report. In my case, I am linking to the final output from Excel, to be used on an external dashboard as a chart data source.

 

[vbaInet]

Usually summarised data is a very small dataset so it shouldn't matter much. The only extra optimisation technique you can employ is to convert the domain aggregate function (i.e. DSum()) to a subquery.