Capturing Previous Month's Transactions Total for Fiscal Year

[koolwaters]

Hi,

I have a database which captures quite a bit of data, among them financial transactions.

At the beginning of each month a number of reports are generated for the previous month's transactions, most of which are exported to Excel and works perfectly.

We have now been given an annual budget where we need to capture the budget for each month, the income generated from a specific set of transactions, whether there was a surplus or deficit, YTD budgeted income, etc.

I created a template in Excel, which captures the information for the transactions and I have formulas set up to calculate the Surplus/Deficit, YTD Budgeted Income, YTD Acutal Income and the Overall Surplus/Deficit.

Now to my issue. Our financial year begins on April 1, so for each preceding report, I need to get the actual income of the previous months.

For example, if I run the report for October, in my Excel spreadsheet, I need to display the actual income for April, May, June, July, August and September in individual cells.

My challenge is that I cannot think of way to store this information in the database as it breaks the normalization rules if I create a table.

Any suggestions would be appreciated.

Thanks in advance.

Cheers!

 

[xenou]

Some options:

• A cross-tab query can create this kind of dataset for you, in Access.
• Also, import the "raw data" to a table on separate (perhaps hidden) spreadsheet and use Excel formulas to populate the actual report from the imported data (that would probably be my preference.
• You could also handle this on the Access side with a query that gets you the de-normalized view of the data with the income in columns for the last four months, etc. (with or without using a crosstab query)

You are probably already doing so, but in general, try to think in terms of offsets from the current month (CurrentMonth, LastMonth or CurrentMonth-1, LastLastMonth or CurrentMonth-2, and so on). A small reference "table" could help with that, or simply extra logic in formulas to calculate the relevant periods.This keeps everything dynamic regardless of the current month you are in, and makes your SQL and Formulas more robust. Also if possible, make sure it "just works" when you flip to a new fiscal year, or works with a minimal amount of manual intervention.

 

[koolwaters]

I have created a budget table, where I have the date for the budget (the 1st of each month) and the budget figure.

I have created a query to summarize the actual income and pull in the budget income for the corresponding month as well.

What I now want to do is be able to filter this data by the fiscal year, which runs from April to March.

For example, if I were to run a report to for January of this year, I would like the query to show transactions from April 2016 - March 2017. If I were to run a report for July of this year, I would like the query to show transactions from April 2017 - March 31. There will be no actual income figures for this last date range but there will be budget figures.

Maybe I am overthinking this, but it seems to be going right over my head.

 

[xenou]

I would have a budget ID as a Foreign Key for each row in my Budget table.
Then the budget ID would be in a Budgets table (or BudgetIDs, whatever you call it) which gives the name, description, and effective dates of the budget - whatever other information seems appropriate as being general to a budget.

Now you just filter for the correct budget ID.