Need sum of previous 12 month rolling period 1

[NeilT123]

Hi all, I have the following query

SELECT tblFieldDetails.FieldCode, tblFieldDetails.FieldName, tblOMApplications.OMApplicationMonth, Sum(qryrpt10FTotalOMNP1.TotalNAppliedByOM) AS SumOfTotalNAppliedByOM
FROM (qrySLTReport INNER JOIN tblFieldDetails ON qrySLTReport.FarmAccountNumber = tblFieldDetails.FarmAccountNumber) INNER JOIN (tblCropping INNER JOIN (tblOMApplications LEFT JOIN qryrpt10FTotalOMNP1 ON tblOMApplications.OMApplicationIndex = qryrpt10FTotalOMNP1.OMApplicationIndex) ON tblCropping.CroppingNumber = tblOMApplications.CroppingNumber) ON tblFieldDetails.FieldCode = tblCropping.FieldCode
GROUP BY tblOMApplications.IncludeInReports, tblFieldDetails.FieldCode, tblFieldDetails.FieldName, tblOMApplications.OMApplicationMonth
HAVING (((tblOMApplications.IncludeInReports)=Yes));

This returns data like this

FieldCode FieldName OMApplicationMonth SumOfTotalNAppliedByOM
-2014214583 Seven Acres Jul-06 99
-2014214583 Seven Acres Apr-09 60
-2014214583 Seven Acres Feb-11 99
-2014214583 Seven Acres Mar-13 91
-1833868285 Earth Down Apr-07 165
-1794524754 Stable Down Dec-04 111
-1794524754 Stable Down Dec-07 24
-1794524754 Stable Down Feb-08 39
-1794524754 Stable Down Aug-12 50
-1671606118 Lane Field Jan-08 42
-1671606118 Lane Field Feb-08 27
-1671606118 Lane Field May-08 50
-1671606118 Lane Field Jun-08 39
-1671606118 Lane Field Dec-08 88
-1671606118 Lane Field May-09 60
-1671606118 Lane Field Feb-12 91

Sorry about the formatting

I need to create a query which gives a rolling previous 12 months sum of SumOfTotalNAppliedByOM so looking at the Feb-12 entry for Lane Field the total would be 91 but for the May-09 entry the total would be 39+88+60 = 187.

Can anyone help with this?

As always thank you for any suggestions.

 

[trevil620]

What heading/value do you want for the 'prior 12 months'? I was too lazy to use your data, but look at the following for a general idea... what you need is the 'IIF' (and in the GROUP BY)
SELECT DataError.SoftwareVersion, IIf(Month([EnteredDate])=Month(Date()),Format([entereddate],"mmm-yy"),'Prior 12<CHANGE>') AS MyDate,
Count(DataError.Number) AS CountOfNumber
FROM DataError
GROUP BY DataError.SoftwareVersion, IIf(Month([EnteredDate])=Month(Date()),Format([entereddate],"mmm-yy"),'Prior 12<CHANGE>')
ORDER BY DataError.SoftwareVersion;


Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[MajP]

So yes you can do calculate functions built on values from tables, and then compute immediately in a query.

Suppose I have several fields in a table (fld1, fld2, fld3) and I need to do something complicated. I would go to VBA

Public Function YourFunctionName(val1, val2, val3) as SomeDataType
Do something complicated here in code
end Function

Then in your query you can use the function and pass values from fields.
Select ..., YourFunctionName([fld1],[fld2],[fld3]) As SomeName, otherField, from someTable

 

[dhookom]

What does your final output need to look like? How many records? What are the columns?

Is "Feb-12" February 12 or February of 2012?

Don't apologize for formatting, just use the TGML code tag and space your columns to provide a good view.

FieldCode    FieldName      OMApplicationMonth	SumOfTotalNAppliedByOM
-2014214583  Seven Acres    Jul-06                  99
-2014214583  Seven Acres    Apr-09                  60
-2014214583  Seven Acres    Feb-11                  99
-2014214583  Seven Acres    Mar-13                  91
-1833868285  Earth Down     Apr-07                 165
-1794524754  Stable Down    Dec-04                 111
-1794524754  Stable Down    Dec-07                  24
-1794524754  Stable Down    Feb-08                  39
-1794524754  Stable Down    Aug-12                  50
-1671606118  Lane Field     Jan-08                  42
-1671606118  Lane Field     Feb-08                  27
-1671606118  Lane Field     May-08                  50
-1671606118  Lane Field     Jun-08                  39

I expect I would use a subquery but can't be sure.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

sorry looks like I posted in the wrong thread. disregard.

 

[dhookom]

MajP,
I was beginning to wonder if you had your

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Hi Duane, Feb-12 is the month of February in 2012

Ideally the output needs to be as per the existing output (as above) but with one more column which can be called something like 12monthsumofTotalN

 

[dhookom]

I assumed the name of you query provided was ttNeilT

Try this SQL:

SELECT ttNeilT.FieldCode, ttNeilT.FieldName, ttNeilT.OMApplicationMonth, ttNeilT.SumOfTotalNAppliedByOM, 
(SELECT Sum(SumOfTotalNAppliedByOM)
 FROM ttNeilT A
 WHERE A.FieldCode = ttNeilT.FieldCode AND A.OMApplicationMonth BETWEEN DateAdd("yyyy",-1,ttNeilT.OMApplicationMonth) + 1 AND ttNeilT.OMApplicationMonth) AS Running12Mths
FROM ttNeilT;

FieldCode    FieldName    OMApplicationMonth  SumOfTotalNAppliedByOM  Running12Mths
-2014214583  Seven Acres  7/1/2006             99                     99
-2014214583  Seven Acres  4/1/2009             60                     60
-2014214583  Seven Acres  2/1/2011             99                     99
-2014214583  Seven Acres  3/1/2013             91                     91
-1833868285  Earth Down   4/1/2007            165                    165
-1794524754  Stable Down  12/1/2004           111                    111
-1794524754  Stable Down  12/1/2007            24                     24
-1794524754  Stable Down  2/1/2008             39                     63
-1794524754  Stable Down  8/1/2012             50                     50
-1671606118  Lane Field   1/1/2008             42                     42
-1671606118  Lane Field   2/1/2008             27                     69
-1671606118  Lane Field   5/1/2008             50                    119
-1671606118  Lane Field   6/1/2008             39                    158
-1671606118  Lane Field   12/1/2008            88                    246
-1671606118  Lane Field   5/1/2009             60                    187
-1671606118  Lane Field   2/1/2012             91                     91

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Hi Duane, normally when I get help from this forum I try and understand what has been suggested so that I can learn. This time I have no idea what your code does, but it works and so all I can say is thank you very much.

Neil

 

[dhookom]

The query with a subquery works by running the subquery for every record in the main query.

This section states: Go back to the same table (notice the FROM ttNeilT) and find the Sum of SumOfTotalNAppliedByOM where the FieldCode values are the same and the OMApplicationMonth is between a range.

(SELECT Sum(SumOfTotalNAppliedByOM)
 FROM ttNeilT A
 WHERE A.FieldCode = ttNeilT.FieldCode AND A.OMApplicationMonth BETWEEN
 DateAdd("yyyy",-1,ttNeilT.OMApplicationMonth) + 1 AND ttNeilT.OMApplicationMonth)

Duane
Hook'D on Access
MS Access MVP