need to code SQL MS Access for running sum partitioned data
Budget: €12 – €18 EUR
I have gotten so far as to get a rolling sum of KWH, the data is sorted by PPL Center , and month.
SELECT Product_Consume.[PPL Centers], Product_Consume.[Month No] AS MoNu, Sum(Product_Consume.KWH_Produced) AS SumOfKWH_Produced, DSum("KWH_Produced","Product_Consume","[Month No]<=" & [MoNU]) AS RunTot
FROM Product_Consume
GROUP BY Product_Consume.[PPL Centers], Product_Consume.[Month No]
ORDER BY Product_Consume.[PPL Centers], Product_Consume.[Month No];
This works.
Now I need to partition this so that the sum is for each PPL Center over a rolling 12 months.
This is not working.
SELECT Product_Consume.[PPL Centers], Product_Consume.[Month No] AS MoNu, Sum(Product_Consume.KWH_Produced) AS SumOfKWH_Produced, Sum(KWH_Produced.Product_Consume), OVER(PARTITION BY Product_Consume.[PPL Centers], ORDER BY Product_Consume[Month No]) AS RunTot
FROM Product_Consume
GROUP BY Product_Consume.[PPL Centers], Product_Consume.[Month No]
ORDER BY Product_Consume.[PPL Centers], Product_Consume.[Month No];
SELECT Product_Consume.[PPL Centers], Product_Consume.[Month No] AS MoNu, Sum(Product_Consume.KWH_Produced) AS SumOfKWH_Produced, DSum("KWH_Produced","Product_Consume","[Month No]<=" & [MoNU]) AS RunTot
FROM Product_Consume
GROUP BY Product_Consume.[PPL Centers], Product_Consume.[Month No]
ORDER BY Product_Consume.[PPL Centers], Product_Consume.[Month No];
This works.
Now I need to partition this so that the sum is for each PPL Center over a rolling 12 months.
This is not working.
SELECT Product_Consume.[PPL Centers], Product_Consume.[Month No] AS MoNu, Sum(Product_Consume.KWH_Produced) AS SumOfKWH_Produced, Sum(KWH_Produced.Product_Consume), OVER(PARTITION BY Product_Consume.[PPL Centers], ORDER BY Product_Consume[Month No]) AS RunTot
FROM Product_Consume
GROUP BY Product_Consume.[PPL Centers], Product_Consume.[Month No]
ORDER BY Product_Consume.[PPL Centers], Product_Consume.[Month No];