Trailing 3 month values

=?Utf-8?B?TmlrZXNo?=
Guest
Posts: n/a

 17th Apr 2007
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

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

Nikesh

John W. Vinson
Guest
Posts: n/a

 17th Apr 2007
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
>
>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]

=?Utf-8?B?TmlrZXNo?=
Guest
Posts: n/a

 17th Apr 2007
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
> >
> >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]
>

John W. Vinson
Guest
Posts: n/a

 18th Apr 2007
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 *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]

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post ryguy7272 Microsoft Access Queries 2 7th Feb 2010 04:28 PM John Spencer Microsoft Access Queries 0 28th Jan 2009 03:47 PM Aaron Microsoft Excel Worksheet Functions 9 2nd Feb 2008 12:24 AM JMoore0203 Microsoft Outlook Calendar 1 5th Dec 2007 05:15 PM jake_allen10@hotmail.com Microsoft Outlook 0 3rd Nov 2006 08:30 PM

Features