Re: Trailing 3 month values

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

.

Relevant Pages

  • Re: Use an update query and offset records
    ... I have a list of employees in another table: ... I have x amount of records in Table 1. ... "John W. Vinson" wrote: ...
    (microsoft.public.access.queries)
  • Re: total query with last date
    ... On Jun 19, 8:59 pm, John W. Vinson ... ID PersonID Date Amount and Type ...
    (microsoft.public.access.queries)
  • Re: table not updated
    ... Everytime I enter it, it ... John W. Vinson wrote: ... 0)*Nzwhich shows the Amount fine. ...
    (microsoft.public.access.queries)
  • Re: total query with last date
    ... On Jun 19, 8:59 pm, John W. Vinson ... ID PersonID Date Amount and Type ...
    (microsoft.public.access.queries)
  • Re: Overcarbonation or something else?
    ... On Fri, 27 Oct 2006 12:03:24 GMT, John Bleichert ... if you used an excessive amount of priming. ... DME varies in fermentability with brand. ...
    (rec.crafts.brewing)