Trailing Twelve Month Formula using MS Access Database

Question

I have a database named Cash_Flow_Statements and I need to create a saved query that calculates a Trailing Twelve Month (TTM) Free Cash Flow.

Here is an example of my database:

And here is the code for my saved query. I have used this code for other calculations so I know it works. I just don't know how to do a Trailing Twelve Month in SQL.

CREATE PROC " & Calculation & " AS _
SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, _
" & Formula & " AS TTM _
FROM (Income_Statements AS Income_Statements INNER JOIN Balance_Sheets AS Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)) _
INNER JOIN Cash_Flow_Statements AS Cash_Flow_Statements ON (Balance_Sheets.Ticker = Cash_Flow_Statements.Ticker) AND (Balance_Sheets.[Year] = Cash_Flow_Statements.[Year]) AND (Balance_Sheets.Period = Cash_Flow_Statements.Period)

I need the variable Formula to contain the equation that will calculate the Trailing Twelve Month for Free Cash Flow. Here is what the math looks like:

Free Cash Flow TTM = Sum(Operating Cash Flow MRQ1 + Operating Cash Flow MRQ2 + Operating Cash Flow MRQ3 + Operating Cash Flow MRQ4) - Sum(Capital Expenditures MRQ1 + Capital Expenditures MRQ2 + Capital Expenditures MRQ3 + Capital Expenditures MRQ4)

MRQ = Most Recent Quarter

I need this formula to be in SQL. How do I do this?

UPDATE

Per @Gord Thompson's answer below I have come up with this code:

SELECT Ticker, [Year], Period, (SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) _
FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements _
UNION ALL _
SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements) su _
WHERE su.Ticker = s.Ticker AND su.[Year] = s.[Year] AND (su.Period Between s.Period - 3 And s.Period)) _
AS SalesLast12Months _
FROM Cash_Flow_Statements AS s

When this is run by itself it returns exactly what I need. But as I mentioned in my original post I need to be able to set the variable Formula to contain the SQL statement and then insert it into the existing SQL statement.

So I've tried to adapt @Gord Thompson answer and I've come up this this.

Formula:

Formula = “(SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) _
FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements UNION ALL SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements) su _
WHERE su.Ticker = Cash_Flow_Statements.Ticker AND su.[Year] = Cash_Flow_Statements.[Year] AND (su.Period Between Cash_Flow_Statements.Period - 3 And Cash_Flow_Statements.Period))”

SQL statement:

"CREATE PROC " & Calculation & " AS SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, " & Formula & " AS TTM _
FROM (Income_Statements AS Income_Statements _
INNER JOIN Balance_Sheets AS Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)) _
INNER JOIN Cash_Flow_Statements AS Cash_Flow_Statements ON (Balance_Sheets.Ticker = Cash_Flow_Statements.Ticker) AND (Balance_Sheets.[Year] = Cash_Flow_Statements.[Year]) AND (Balance_Sheets.Period = Cash_Flow_Statements.Period)

This is the result:

Again this is very close but not quite what I need. I believe that I know what causes the code to not work when I adapt it but I don't know how to fix it yet. In @Gord Thompson code the last line is FROM Cash_Flow_Statements AS s. When I insert the variable formula into my existing SQL statement from my original post, the code is FROM (Income_Statements AS Income_Statements _ INNER JOIN Balance_Sheets AS Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)) _ INNER JOIN Cash_Flow_Statements AS Cash_Flow_Statements ON (Balance_Sheets.Ticker = Cash_Flow_Statements.Ticker) AND (Balance_Sheets.[Year] = Cash_Flow_Statements.[Year]) AND (Balance_Sheets.Period = Cash_Flow_Statements.Period). I'm almost positive that this is where the problems lies but do not know how to correct it.

Answer 1

The variable Formula should contain this:

SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures FROM Cash_Flow_Statements UNION ALL SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures FROM Cash_Flow_Statements) su WHERE su.Ticker = c.Ticker AND su.[Year] = c.[Year] AND (su.Period Between c.Period - 3 And c.Period)