CODEWITHSUNDEEP
ms-accesscumulative-sum
Brian J
Brian J

Reputation: 1

Issue with Running total in Access

I am trying to add a running total to an access query and after watching a video on how to do it I came up with the following;

SELECT Analyst, [Grant ID], [Fiscal Year], Available, Recovered, Bal,

(SELECT SUM(Bal) FROM QueryTest WHERE C.[Fiscal Year] >= [Fiscal Year] and C.[Grant ID] = [Grant ID]) as
RunningBalance

From QueryTest AS C

ORDER BY Analyst, [Grant ID];

but when I run it it indicates that

the database engine does not recognize C.[fiscal Year] as a valid field suggesting something is wrong with the use of the alias but I cant figure out what it is.

Any help is appreciated.

Upvotes: 0

Views: 69

Answers (1)

June7
June7

Reputation: 21370

Try the C alias in the inner query not the outer.

SELECT Analyst, [Grant ID], [Fiscal Year], Available, Recovered, Bal,

(SELECT SUM(C.Bal) FROM QueryTest AS C 
 WHERE C.[Fiscal Year] >= QueryTest.[Fiscal Year] 
 AND C.[Grant ID] = QueryTest.[Grant ID]) as RunningBalance

FROM QueryTest

ORDER BY Analyst, [Grant ID];

A simpler alternative is to build a report and use textbox RunningSum property.

Upvotes: 1

Related Questions