Re: Trailing 3 month values
- From: John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 17 Apr 2007 15:22:50 -0600
On Tue, 17 Apr 2007 12:56:03 -0700, Nikesh <Nikesh@xxxxxxxxxxxxxxxxxxxxxxxxx>
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]
.
- Follow-Ups:
- Re: Trailing 3 month values
- From: Nikesh
- Re: Trailing 3 month values
- Prev by Date: Re: Parameters in queries
- Next by Date: Re: Trailing 3 month values
- Previous by thread: Re: Parameters in queries
- Next by thread: Re: Trailing 3 month values
- Index(es):
Relevant Pages
|
