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

On Tue, 17 Apr 2007 12:56:03 -0700, Nikesh <(E-Mail Removed)>
wrote:

>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]

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" wrote:

> On Tue, 17 Apr 2007 12:56:03 -0700, Nikesh <(E-Mail Removed)>
> wrote:
>
> >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]
>

On Tue, 17 Apr 2007 15:12:01 -0700, Nikesh <(E-Mail Removed)>
wrote:

>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]