Current and YTD in one query?

[MarshaPav]

I have a file that looks like this:

Account Month Amount
1001 1 100.00
1001 3 200.00
1001 4 300.00
1002 1 400.00
1002 2 500.00
1002 3 600.00

I want a query that produces this (for March):
Account Current YTD
1001 200.00 300.00
1002 600.00 1500.00

Can this be done in one query? I can get either the current or the YTD, but haven't been able to get both in one query.

 

[Remou]

Perhaps something like:
[tt]SELECT tblTable.Account, tblTable.Month, tblTable.Amount, (Select Sum([Amount]) From tblTable A Where A.Account = tblTable.Account And A.[Month]<=tblTable.[Month]) AS YTD
FROM tblTable
WHERE (((tblTable.Month)=3));[/tt]

 

[PHV]

Something like this ?
SELECT A.Account, A.Amount AS [Current], Sum(B.Amount) AS YTD
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Account = B.Account
WHERE A.Month = 3 AND B.Month <= A.Month
GROUP BY A.Account, A.Amount

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