Running 12 Months Query by Period Value

[jerbaldw]

I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.

The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.

I appreciate any help!

Thanks.

 

[MarkK]

Hello. Welcome to the forum.

There are no visible dates in the data in your jpg. It is impossible to implement a rolling 12 month constraint if there are no dates in the data.

 

[jerbaldw]

So there is no way to utilize the lng_PERIOD values? I thought something like the string below would be possible..?

lng_PERIOD >= lng_PERIOD-11

 

[MarkK]

It's your data. If you've encoded dates in there somewhere, you need to say where, and how to decode them, because they are not immediately apparent. Also, if you are talking about a 12 month rolling average--is that what you mean?--we'll need to know what value to average.

Hope this helps,

 

[jerbaldw]

After some internet searching and manipulating I was able to come to the following solution using nested queries with a between operator.

thanks you for the attempt.

SELECT t1.lng_PERIOD, t1.chr_EE_RACF, t1.lng_INDIVIDUAL_TPROCESSING, (
        SELECT Sum(clng(t2.lng_INDIVIDUAL_TPROCESSING))
            FROM tbl_t_processing AS t2
            WHERE
                t2.chr_EE_RACF = t1.chr_EE_RACF
                AND clng(t2.lng_PERIOD) BETWEEN (clng(t1.lng_PERIOD) - 11) AND clng(t1.lng_PERIOD)
    ) AS lng_INDIVIDUAL_TPROCESSING_ROLLING
FROM tbl_t_processing AS t1;