Running total - almost working but needs refining!

[Big Pat]

Hi,

My table contains a MonthNumber (values 1 to 12) and fields called 'Y10-11', 'Y11-12', etc. which contain numbers of widgets (let’s say) produced in that month. I need a query to calculate running totals for each year and found this thread very helpful (thanks Mahjenk2)

Using that I came up with this:

IIf([Y11-12] Is Null,Null,Nz(DSum("[Y11-12]","MCRNByMonth","[MonthNumber]<=" & [ExistingMonthNo]),0))

...and corresponding functions for the other years.

I added the If...Is Null part to stop the query from carrying the current value forward for months that haven’t elapsed yet. However that has produced a minor problem. In my data, there were no widgets produced in month 11 of Y10-11. Therefore the query does not return a cumulative value for that month, but goes back to normal the next month.

The attached report will probably explain it better. I’m charting it too for a visual comparison between years and you can see the corresponding gap in the results. I guess I could find the option to interpolate the missing data rather than show a gap, but for “tidiness”, I’d like to get the query to return the right value so it can be included in the report.

But the solution must still allow for the future months (in the current year) to show null.

Any ideas how this can be done?

Thanks,

Pat.

 

[plog]

To briefly answer your question before getting to the real issue: Access can't make up data--it doesn't know to fill in gaps, because it sees no gaps. This means you need a data source without gaps. You need a table that has all the months and years you want to report on.

However, I'm fairly certain your table isn't normalized. You should not have field names that contain data themselves. You shouldn't have a field called 'FY10-11', instead you should have a field to hold the year data (something called 'YearData', or 'ReportYear'). You are going to have more issues than what you are facing now unless you get this fixed.

 

[Big Pat]

Hi,

I appreciate that Access can't make up data, but it can write data to a new table which is what I'm doing here. My 'raw' data actually consists of individual dates on which certain processes were completed, over several years. But in order to compare financial years and get the report layout as shown, I thought it made sense to aggregate the data using a make table query and then base my report on that table. That's the only thing that table would be used for. For all other analyses I'm going back to my source data.

So in this example, where there was no underlying data for Feb-11, is there a way to get the make table query to write the previous running total into the table? If this was just a one-off scenario, then I could of course do it manually, but I need to run this for hundreds of different TYPES of processes, by changing parameters on a form.

Having said that, for most types it will be a rare occurence, so it's not the end of the world if it's too tricky.

Thanks for your help.

 

[plog]

As I said before, if you want to report on data that isn't there, you have to provide a source in which Access can find that data. In your instance this means creating a table with all the reporting periods in it.

You want to run your report and include months and year combinations that aren't in the data. This means you need a table that provides this. Create a table that has all the month/year permutations you need, bring that into your query as well as your data source you are currently using. Use a LEFT JOIN from the month/year permutation table into the data table you are currently using. This will bring all the month/year permutations into your data source and force them to appear for reporting.

 

[Big Pat]

Thanks plog. That's pretty much the conclusion I was coming to as well. I was hoping there was some way the existing running total expression could be altered to allow for this, but I do understand your point and the more I think about it, the more I'm sure you're right!