Rolling total....again

[wlrottge]

I know this has been covered several times, however I cannot find a solution
to my problem.

ANY solution will work, does not have to be using the code I post here

I am trying to run a 12 month rolling total in access with this line of code.

I have a query that pulls the data that I want out and filters and sums it to
fit my date range. "02c-02 Sum individual HAPS"

The fields in this query are:
TIME_FROM (GROUP BY)
POLLUTANT_CAT_CD (GROUP BY)
EMISSION_LBS (SUM)

I am trying to run my next query "02c-03 Rolling HAP Sum" with the following
fields:
POLLUTANT_CAT_CD
TIME_FROM
SumOfSumOfEMISSION_LBS

the last field is:
Rolling_Total: DSum("[Rolling_Total]","[02c-02 Sum individual HAPS]","
[TIME_FROM]Between #" & [TIME_FROM] & "# And DateAdd(""D"", -366, #" &
[TIME_FROM] & "#)")

I don't get any error messages, but I don't get any results either.

Any help would be GREATLY appreciated.

Thanks,

Will R

 

[Tom Ellison]

Dear Will:

There are some simple factors to be observed:

1. In what order is the total to be added.

2. Does the total start over according to some GROUP (set of columns, in
which the change of any starts the total over - must be the highest order
columns in the sort order.

Also, you ask for a rolling total. This would be a total of, say the most
recent 6 rows, or all the rows within a period of 90 days. A running total
is something much more common. I want to make certain which it is you want.

The easiest way to help specify this and get a working query in return is to
post a query that has everything BUT the total column included. This can be
most easily modified to give you the result you want. You should still
answer the above questions for complete clarity.

Tom Ellison

 

[wlrottge]

what I have is a series of emissions over the past 24 months.
1/1/04 - 1/31/04 31 lbs
2/1/04 - 2/28/04 28 lbs
3/1/04 - 3/31/04 32 lbs
and on and on....

My table will continue to grow from now till... whenever.

What I am doing is setting a date range (say 1/1/2005-12/31/2005) where
I have a monthly number that I want to add cumulatively, where you get
in this example:
January's number, total (Jan + previous 11 months)
Feb's number, total (Feb + previous 11 months)
and on to Dec, total (Dec + previous 11 months)

My query pulls all the required data from the start date back 11
months, but I can't seem to get the rolling 12 month number to work. I
used some Dsum code I found, but no luck as of yet.

To answer your questions,
1 - added in order of their date (jan+past 11, jan+feb+past 10,
jan+feb+mar+ past 9...)
2 - The total "rolls" depending upon the start date.

I can send you a cut down version of the db if you want to see
specifics.

Thanks a LOT for your help!

 

[Tom Ellison]

Dear Will:

So, it really is a rolling sum. OK.

Now, you've thrown me another curve. I don't know column names and types.
This is going to work on what, 3 columns of data in a table or query?

Something like this:

SELECT Month(StartDate) AS XMonth, Year(StartDate) AS XYear,
(SELECT SUM(Weight)
FROM YourTable T1
WHERE T1.StartDate BETWEEN
DateAdd("m", -12, T.StartDate)
AND StartDate) AS RollingSum
FROM YourTable T
ORDER BY StartDate

Change the table and column names to fit. Try not to change anything else
initially.

Tom Ellison

 

[wlrottge]

Thanks, I've got it working now!!!!!