Our physical year started October 1, 2012 for physical year (2013). My data will be fine until next year, October 1, 2013 for the new physical year (2014). My main question is: how can I have (2014) reflect in my reports and split forms without building in specific criteria to the query, especially on the report on the main page? And yes, I want to force the user to view the current physical year unless they need to view the previous years for reporting purposes.

My proposed solution is to create something that is automated. For example, when the new date is 10/1/2013, Id like to create a VBA command button for the Admin user that will archive my current information to ArchiveBudgetT, ArchivePurchaseOrderT, and ArchiveRequisitionOrder by doing an Append, Update and Delete Query.

The budget categories and amounts change year to year, depending on whom the Admin that handles Budget for that year. Later, I could create queries for the data in the Archive tables for the date ranges/physical year.

By doing this, when admin support begins entering values for the New Year, I wont have a list of 20 budget categories in the split form / table for next year. Nor will I have long list of 6000 purchase orders, as time goes on that list with multiply every year. My proposed solution will have just the current years information instead of viewing previous years in current reports / split forms. In my reports, I run queries for pending, canceled, and compete so I rather not add year to the mix as well.. I feel if I dont Append, Update and Delete, my reports will be hundreds of pages. The less interaction the user has in creating reports, the better, at least for the first couple of months of testing this database.

For the report on my main page, it is not a subForm, its a report built into the main page. When the main page is On_Focus, it automatically refreshes the data to the most current. The Source Object is Report.CurrentTotalsMain.

Do you think setting something like this is unreasonable? I already have an Append, Update and Delete Query for Budget Transfers when I transfer money from one budget to another depending on need. I did this to prevent the Admin user from accessing the tables.

---

Reply from Richard Rost:

You could certainly do that with multiple tables. OR you could just make a function to determine the Budget Year based on a date that was entered. So if 11/1/2012 was sent to it, it would return 2013. Then you could use this values in all of your calculations that rely on budget year.

Then if you WANT TO you could archive all of your data to an old table once your year-end reports were finalized, but you wouldn't NEED to because it wouldn't matter if extra data was in your table.