Help with Tricky Sum Calculation

[ejc00]

I have a table that has data organized as below:
ID Month Amount
1 1 100
1 2 125
1 3 200
1 4 210
1 5 30
1 6 70
2 1 10
2 2 37

What I need to do is have a sum which satisfies the following IF statment:
If Month > (CurrentMonth - 2) AND Month < CurrenMonth Then
Sum(Amount)

But it needs to be a running sum... meaning that I need to calculate it for month 3, 4, 5, 6, etc. Associating each sum with the given month. In English, for each month, I need to calculate the amount for the previous 3 months.

I can't think of a quick way to do it in a query or in VBA... currently, I'm using recordsets to loop through and calculate it individually, but this takes FOREVER.

Thanks,
Evan

 

[BillPower]

Before I could attempt to answer this, what would you expect to happen in Month 1 and Month 2, shouldn't you have a Year identifier???

One other thing that concerns me, 45 questions and only 1 marked helpful. Also I think lespaul's suggestion deserves acknowledging in thread701-597787 among other suggestions made by members.

Bill

 

[ejc00]

bill...

thanks for your remarks. i just recently realized you could mark responses as helpful... i guess i just didn't really pay that much attention to it, or thought that the site administrators marked different suggestions.

going forward... it's something i will definitely make sure to be aware of.

for the task at hand... the months are numbered 1-150... months 1 and 2 would not be figured into the calculation by way of an IF statement. i figured out a way to calculate this using recordsets... but if you know a way to do it using queries, i'm all ears.

thanks again,
ejc