Trailing 3 month values

[Guest]

I have a table silimar to one below but with a lot of extra variables.
Essentially, what I need to calculate is the sum of amount for the recent
three months for each month.
ID Month Amount
1 200606 100
2 200607 25
3 200608 34
4 200609 54
5 200610 56
6 200611 76
7 200612 123
8 200701 56
9 200702 78
10 200703 56

The additional column I would need from the column above is as follows:

ID Month Amount TR3_Amount
1 200606 100 100
2 200607 25 125
3 200608 34 159
4 200609 54 113
5 200610 56 144
6 200611 76 186
7 200612 123 255
8 200701 56 255
9 200702 78 257
10 200703 56 190

I was able to get this column using the following query:

SELECT a.ID, a.Month, a.Amount, sum(b.Amount) AS TR3_Amount
FROM TR3 AS a, TR3 AS b
WHERE a.ID>=b.ID and a.ID<b.ID+3
GROUP BY a.ID, a.Month, a.Amount;

This would perfectly serve my purpose if my table was smaller.
Unfortunately, with the 30,000 records I have in my table, it takes too long
for Access to calculate. Does anyone have a different idea?

I am a beginner in Access and don't know much VBA. Is there any other
solution to this problem using SQL?

Thanks for all your help.
Nikesh

 

[John W. Vinson]

I have a table silimar to one below but with a lot of extra variables.
Essentially, what I need to calculate is the sum of amount for the recent
three months for each month.
ID Month Amount
1 200606 100
...
I was able to get this column using the following query:

SELECT a.ID, a.Month, a.Amount, sum(b.Amount) AS TR3_Amount
FROM TR3 AS a, TR3 AS b
WHERE a.ID>=b.ID and a.ID<b.ID+3
GROUP BY a.ID, a.Month, a.Amount;

Well, this assumes a) that the ID's are absolutely guaranteed to be sequential
and b) that you'll never have duplicate values of month.

This would perfectly serve my purpose if my table was smaller.
Unfortunately, with the 30,000 records I have in my table, it takes too long
for Access to calculate. Does anyone have a different idea?

I am a beginner in Access and don't know much VBA. Is there any other
solution to this problem using SQL?

Are there any indexes on your table? Right now you're doing a Cartesian join,
and Access is having to process 900,000,000 possible combinations - no wonder
it's slow!

IF - and it's a big if, and makes me queasy - you can count on ID being
sequential, put a unique Index on it (unless it's already the primary key) and
try a Self Join:

SELECT A.Month, A.Amount + B.Amount + C.Amount
FROM FROM (TR3 AS A INNER JOIN TR3 AS B ON A.ID = B.ID+1) INNER JOIN TR3 AS C
ON B.N = C.N+1;

What's the datatype of your (badly named) Month field? If it were a Date/Time
you could use IT to join, and not worry about the ID's being precisely in
order...

John W. Vinson [MVP]

 

[Guest]

Thanks for the quick reply John.

I actually do not have any indexed variable in my table. I was actually
thinking of arranging my tables by my variables and finally by month. I was
then thinking of creating an autonumber variable as ID. I do have duplicate
values of month in my table. My month variable is a long integer (thats how
it comes when I pull the data from my database).

Below is a more clear picture of how my table looks like and how I need the
TR3 amount to be displayed. For each unique combinations of the variables
State and Item, I have the amount for each month. I do have couple other
variables in my table, making the total number of unique records around 30K.

State Item Month Amount TR3 Amount
AK A 200601 27
AK A 200602 84
AK A 200603 34 145
AK A 200604 69 187
AK A 200605 35 138
AK A 200606 6 110
AK B 200601 48
AK B 200602 68
AK B 200603 31 147
AK B 200604 86 185
AK B 200605 55 172
AK B 200606 89 230
AL A 200601 54
AL A 200602 24
AL A 200603 68 146
AL A 200604 92 184
AL A 200605 60 220
AL A 200606 3 155
AL B 200601 77
AL B 200602 75
AL B 200603 2 154
AL B 200604 18 95
AL B 200605 47 67
AL B 200606 12 77

Thanks again for you help.
Nikesh

 

[John W. Vinson]

Thanks for the quick reply John.

I actually do not have any indexed variable in my table. I was actually
thinking of arranging my tables by my variables and finally by month.

STOP.

Tables ARE NOT SPREADSHEETS!

Tables *have no order*. They're just sacks full of data. It is meaningless to
talk about "arranging tables by my variables". If you want to see data in
order, you store it in a table (in any arbitrary order) and use a Query to
sort it.

I was
then thinking of creating an autonumber variable as ID. I do have duplicate
values of month in my table. My month variable is a long integer (thats how
it comes when I pull the data from my database).

I'd really, really suggest recasting it as a Date/Time, which will let you use
the powerful date functions in Access in your query. If you add a field
TransDate, you can populate it from your [Month] field by running an Update
query, updating it to

CDate(Format([Month], "0000\-0\-\0\1"

converting 200601 to 2006-01-01 and then to a Date/Time.

You can then use a query with a subquery:

SELECT State, Item, TransDate, (SELECT Sum([Amount]) FROM tablename AS X WHERE
X.State = yourtable.State AND X.Amount = yourtable.Amount AND X.TransDate
BETWEEN DateAdd("m", -3, TransDate) AND TransDate) AS TR3Amount;

Below is a more clear picture of how my table looks like and how I need the
TR3 amount to be displayed. For each unique combinations of the variables
State and Item, I have the amount for each month. I do have couple other
variables in my table, making the total number of unique records around 30K.

Since TR3 Amount can - and should - be calculated on the fly from data which
exists in the table, it *simply should not exist* in your table, period. Just
calculate it. If you have indexes on State, Item and TransDate this query
should be reasonable - 30000 rows is *not* large with properly designed
queries and tables.

John W. Vinson [MVP]