Summing in query at row level

[Mohammad Saeb]

Can anybody help me about this subject?

I have a query like this one

ID Code Amount
1 A 100
2 B 200
3 C 300


I wonder if there is as way to make a fourth column that contain a sum of
all mount ABOVE the current Row.
Like this:
ID Code Amount Sum
1 A 100 100
2 B 200 300
3 C 300 600

Any ideas? thanks for all.

 

[Guest]

a running balance? not possible. you can do that with a
report but not with a query. you can sum the amounts for a
total but you would need a seperate query with the single
column for amount meaning that in the sum query,you would
have to omit ID and Code in your example below.

 

[John Ortt]

I used the following query to do the same on a financial database of my own
(based on help from this NG).

SELECT a.TransDate, a.Account, -First(a.TransAmount) AS
Amount, -SUM(b.TransAmount) AS Balance
FROM [QataComplete] AS a INNER JOIN [QataComplete] AS b ON
(b.TransDate<=a.TransDate) AND (b.Account=a.Account)
GROUP BY a.TransDate, a.Account;

Adapting that I think it would read:

SELECT a.ID, a.Code, First(a.Amount) AS Amount, SUM(b.Amount) AS Total
FROM [Data] AS a INNER JOIN [Data] AS b ON (b.ID<=a.ID)
GROUP BY a.ID, a.CODE;

Replace the [Data] sections with the name of your table or query and paste
the text into the SQL view.

Hope that helps and let me know how you get on..

John

 

[Mohammad Saeb]

thanks , it was very helpful but it has 1 disadvantage: it takes along time
for a middle size query!

I used the following query to do the same on a financial database of my own
(based on help from this NG).

SELECT a.TransDate, a.Account, -First(a.TransAmount) AS
Amount, -SUM(b.TransAmount) AS Balance
FROM [QataComplete] AS a INNER JOIN [QataComplete] AS b ON
(b.TransDate<=a.TransDate) AND (b.Account=a.Account)
GROUP BY a.TransDate, a.Account;

Adapting that I think it would read:

SELECT a.ID, a.Code, First(a.Amount) AS Amount, SUM(b.Amount) AS Total
FROM [Data] AS a INNER JOIN [Data] AS b ON (b.ID<=a.ID)
GROUP BY a.ID, a.CODE;

Replace the [Data] sections with the name of your table or query and paste
the text into the SQL view.

Hope that helps and let me know how you get on..

John