Special Total Query

[yskw1024]

Hi All,

Is there any to create a query like below:

DATE AMOUNT TOTAL
2005/10/18 1000 12600
2005/10/17 1100 11600
2005/10/16 1200 10500
2005/10/15 1300 9300
2005/10/14 1400 8000
2005/10/13 1500 6600
2005/10/12 1600 5100
2005/10/11 1700 3500
2005/10/10 1800 1800

in which the Total = Current Amount + Total in previous date. E.g. On 2005/10/15, Total = 1300 + 8000 = 9300.

Many Thanks for any input.

 

[PHV]

Either:
SELECT A.DATE, A.AMOUNT, (SELECT Sum(AMOUNT) FROM tblAmount WHERE [DATE]<=A.DATE) AS TOTAL
FROM tblAmount AS A
ORDER BY A.DATE DESC;
or:
SELECT A.DATE, A.AMOUNT, Sum(B.AMOUNT) AS TOTAL
FROM tblAmount AS A INNER JOIN tblAmount AS B ON A.Date >= B.Date
GROUP BY A.DATE, A.AMOUNT
ORDER BY A.DATE DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[yskw1024]

Well, this is excatly what a need to do.

Many thanks for your help ; )

I use the second method...