Running total query not calculating first fiscal year

[Sketchin]

I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.

It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:

FYear BudgetedCost IndCont Commitment
2010
2011 8585643 4742000 3843643
2012 22971165 11432165 11539000
2013 36187262 16963282 19223980
2014 44577690 20706644 23871046
2015 49638150 23206644 26431506

As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:

SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));

and the Aggregate query:

SELECT qryDashboardChart1.FYearExport,
 DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "") 
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment]) 
AS IndustryContribution, DSum("[TECTERRACommitment]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "") 
AS RunTotTECTERRACommitment
FROM qryDashboardChart1
GROUP BY qryDashboardChart1.FYearExport
ORDER BY qryDashboardChart1.FYearExport;

I based this strategy off of this article from Microsoft:

http://support.microsoft.com/kb/290136

I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.

 

[vbaInet]

1. Remove the Group By from the query, qryDashboardChart1
2. Use the DISTINCT keyword to return distinct records (if that's the reason why you used GROUP BY in the first place)
3. Save the query in step 2 and create a query based on it, then
4. Perform the running sum in the new query
5. Move IndustryContribution to the last field.

 

[Sketchin]

Thanks for the reply. For the first query I now get a list of fiscal years like this:

FYearExport
2010
2011
2012
2013
2014
2015

For the 2nd query, I am still missing a total for the first row. To make it easier to troubleshoot, I only used 1 calculated expression (BudgetedCost). Here is the SQL of the 2nd query:

SELECT qryDashboardChart1.FYearExport, DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "") 
AS RunTotBudgetedCost
FROM qryDashboardChart1;

This is getting reeeeeealy frustrating! :banghead:

 

[Sketchin]

I can actually get the same (incorrect) results if I have 1 query that looks like this:

SELECT DISTINCT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 
AS FYearExport, DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "") 
AS RunTotBudgetedCost
FROM tblProject
WHERE (((tblProject.FPAccepted)=True));

 

[vbaInet]

Sorry, I think I didn't phrase my first point properly.
The first query should remain as it is, it's the second I meant that you should remove the GROUP BY clause. Is that what you did?

And is the first query returning a value for 2010?

NB: All the points mentioned were for the second query, not the first query.

 

[Sketchin]

Alright, so the first query now returns a list of fiscal years for each project.

The second query still returns this:

FYearExport BudgCost Commit IndContribution
2010
2011 8585643 3843643 4742000
2012 22971165 11539000 11432165
2013 36187262 19223980 16963282
2014 44577690 23871046 20706644
2015 49638150 26431506 23206644

SQL for 1st query:

SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));

2nd Query SQL:

SELECT DISTINCT qryDashboardChart1.FYearExport, DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, DSum("[TECTERRACommitment]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "") 
AS RunTotTECTERRACommitment, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment]) 
AS IndustryContribution
FROM qryDashboardChart1
ORDER BY qryDashboardChart1.FYearExport;

Did I miss something?

 

[vbaInet]

You missed step 3.

If it's still not working, upload a test db.

 

[Sketchin]

Just to clarify, I should end up with 3 queries, the 3rd query being the one with the running sum?

 

[vbaInet]

That's right.