Results 1 to 3 of 3
  1. #1
    ewfranz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Location
    Yakima
    Posts
    1

    Database set up year by year with only reporting for current year.

    I have a database that has about 20 reports and 30 queries. The database tracks teachers budgets and how much they spend. We enter all the items that the teachers have bought in the database. It works great for this year but what about next school year. How do I set it up so that I do not have to delete all the records of transactions that teachers have made over the current year? I pull budgets for teachers off one table that feeds all the rest of the queries and reports. Each year the budgets change for all the teachers. So how do I set it up so that i can use next years school year and I can change the staff table with budgets without changing the current years totals and reports. I will need to do this year after year. I do know that I can duplicate the database and take out the transactions that they teachers have made and change budgets and have a fresh database. But I want to be able to compare the years and months with each other in one database. So next year I want to look at March and see what we spent during the 17-18 school year and compare it with 16-17 school year. i hops this makes sense. Any help would be great.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    hopefully you have a table for school year with a date in it. All classes , budgets would be based on this year (or key)
    make a form to report from.
    the form would have a text box to enter school year.
    the query would pull in this limit.

    query: select * from table where [year]=forms!myForm!txtYear

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The answer is to have a date associated with every record in the database. For the budget the fiscal year can be used (and month?), and all transactions must have a date associated. Then for all your forms/reports/queries, add a selection by fiscal year, or a date range, or whatever is required.

    At the beginning of each year copy all the budget records for the previous year and add them to the same budget table. This would be an append query and you can add it to wherever makes sense in the database. It would need to first check to see if the records exist prior to appending them so that the query doesn't get run multiple times. Again, a fiscal year selection would be entered by the user.

    It would be best to do all of this now and test it by changing dates and data in your test environment.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 01-24-2016, 07:48 PM
  2. Replies: 4
    Last Post: 07-13-2015, 02:06 PM
  3. Replies: 3
    Last Post: 06-22-2015, 06:36 AM
  4. Replies: 1
    Last Post: 04-20-2015, 03:22 PM
  5. Replies: 4
    Last Post: 01-09-2013, 11:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums