Reset New Year
By Richard Rost
23 days ago
Reset an Access Database for the New Year
2025 Repost - In this Microsoft Access video tutorial, I will show you how to set up your database properly without having tables, queries, or other objects that are based on year values.
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, reset new year, reset autonumber, reset number on new year, reset order number, restart numbering
Subscribe to Reset New Year
Get notifications when this page is updated
Intro
In this video, we will talk about how to reset your Microsoft Access database for the new year. I'll discuss common issues such as having separate tables for each year, copying databases annually, using fields like "previous year" or "this year" instead of storing the actual year, and the question of resetting autonumbers. You'll learn the best practices for organizing your data using proper date fields, creating year-based queries, and handling year-specific reporting without needing to make major changes each year.
Transcript
Howdy folks, this is Richard Rost with AccessLearningZone.com. This is a repost of a video that I did last year. Every year about the same time I get the same kinds of emails from people. Everyone always wants to know, how can I reset my Access database for the new year? My database has got tables in it that say things like sales 2023. We are going to talk about that in this video. I'm just reposting it from last year. It's the same stuff I see every year. If you haven't seen this one before, watch and enjoy.
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we are going to talk about resetting your database for the new year.
It always happens. Today is January 5th, the first video of the new year, and this week of the year, almost every year, I see tons of people sending me emails or posting in the newsgroups for resetting their database for the new year. How do I reset my database? If your database is properly built, there's nothing that you need to reset. There's nothing that you should have to change.
If you've got orders or contacts or inventory that's based on dates, you should be using either a proper date field for it or a year field, at least. There's nothing really to reset there.
Let me show you the four things that people always email me that they need help with. I'll show you the four ways that I would address them. This list is by no means exhaustive. I've seen some crazy stuff over the years, but let me show you the four that I always see time and time again. If any of these match your database, pay attention.
Number one is my favorite. I always get people that say, I have my sales or whatever, and each year is in its own table. Sales2019. Sales2020. No, do not do this, because now your queries are based on those tables. If you rename the table, you have to rename the query. If you get into more advanced stuff, especially VB programming, that doesn't happen anymore. You should never have objects in your database that are based on a date value like that. The only exception is if you are archiving data. We will talk about that in a few minutes.
How do you fix that? You store all the records in the same table. All your orders should be in an order table or a sales table or whatever you want to call it. Use proper date fields. I know people want to bring up all the sales from 2020, so they just open up that table. Here is all the sales 2020. What you are going to do is make a query and use proper criteria to view whatever year you want.
This is how you do it. Bring your order date in. You set a value. That is going to be the year of the order date. That is going to return a number like 2020 or 2021. You can either put that number here in the criteria, or you can use a parameter query for that. Then it doesn't matter. You have all 20 plus years of your sales in one table, and you want to see what were the sales for 2018. You pull up the query and you run through 2018.
You need to know how to do Access query criteria, parameter queries if you want to, or you can put in a prompt like "Enter the year." Here is how to use the Year function to pull the year out of a date. For example, if you want 1994.
Moving on, problem two is very similar to problem one, but instead of having different year tables in the database, they copy the whole database. They just set aside, "OK, 2022 is done," so set that one aside. Now the whole table has all the data for that year in it, and all of the queries and reports are just based on that entire table. Now they try to add records for 2023, but the queries and reports are all wrong because it is going to show more than just that year's data.
Again, you solve this the same way that you solved problem one. We are going to use one table, and we are going to have criteria in our queries. Here is where you can archive data. Some people make multiple copies because the database file gets too big. I get it. My database reached the two gigabyte limit about 10 years ago. That is where you can split your database and have different tables in different files. Or you can archive the data.
If you have 20 years' worth of sales data in a table, for example, and you know for a fact you are never going to need stuff from 15 years ago, you can archive that off into an order archive table to make your database smaller. If it turns out that you have to go back and look for something that's 10 years old, you can go back in the archive table. On a daily basis, you might not need that information.
Here is a video on splitting your database if you want to split off the tables into their own files if your main database is getting too big. Here is a video on archiving old records. If you have five or ten years' worth of sales data and you only need the last couple of years in your main database, you can archive them.
Problem three is another big problem that I see. I've seen two people email me about this today. They have built their database with the previous year, this year design. One person was using this year and next year for budgeting. You have one field that is the previous year's value and another field that's this year's value, or this year and next year. This is bad database design. Don't design your database this way because what is previous year? If you open up this database three years from now, what is previous year?
To fix that, you are going to put the year in as a field, budget year for example. Here is your budget year. Then you have your category and your value. If you want to compare 2022's rent to 2023's rent, for example, or do a whole chart of it, you can use a crosstab query to compare year over year.
Here is a video on crosstab queries. Here is a video for doing month over month sales, like comparing February of this year with February of last year. The technique is almost the same for doing year over year. I don't have a year over year video yet. I'm going to make one soon. But this will show you basically what you have to do. Use the Year function instead of the Month function. Same thing.
Finally, problem four is "I need to reset my autonumbers for the new year." No, you don't. You don't change autonumbers. Autonumbers are not for you. You don't touch them. They are for the computer. I have a whole video on this topic. Autonumbers are not for you. You shouldn't care what those numbers are. Those are internal numbers for Access to form relationships and to make sure each record is unique. You don't touch those. It doesn't matter if Jean-Luc Picard is customer four or customer 1701. Doesn't matter.
What you can do, however, is use your own custom sequential counter that is based on the year. This is a little more tricky. It's a little more advanced, but I do have a video for it. If you want your order counter for a customer, for example, to reset, so now his last order was 2022-015, now it is going to reset to 2023-001. That is what this video covers.
Don't rely on autonumbers or anything like this. But one more person emailing me about changing autonumbers... I'm just kidding. I'm here to teach you. Autonumbers are not to be messed with. You don't have to worry about what their value is.
That's pretty much it. Those are the four big ones that people always email me. How do I get my database ready for next year? How do I reset? You shouldn't really have to if you built your database right from the ground up. If not, if you have a situation in your database that is year dependent and every year around the first week of January you have to scramble and make some changes, let me know what it is. I want to know. I am making another video and will add it to the list of things. I've seen a whole bunch of weird stuff.
I do have two more links to show you real quick that are helpful with year-end stuff. I have a video on putting together year-end reporting. That's very helpful. If you use a fiscal year, then I've got a fiscal year seminar that covers all kinds of different ways to work with fiscal year data.
That's it. That's your Fast Tip for today. Welcome to 2023, everyone. Hope you guys had a great New Year's Eve and safe New Year's Day and all that good stuff. I'm looking forward to hearing your comments on what you think about this video, so post something down below. I'll see you next time.
Oh, and yes, this is still my previous microphone that I was trying last week and my new one arrives tomorrow. We should see, hopefully in tomorrow's video, if I have the better mic or not.
OK, bye bye.
Quiz
Q1. What is the best practice for organizing yearly data, such as sales records, in an Access database?
A. Create a new table for each year and name it using the year
B. Keep all sales in a single table and use a date field to identify the year
C. Make a separate database file for each year
D. Only keep the most recent year's sales in one table
Q2. How should you retrieve all the sales for a specific year in your database?
A. Open the table for that specific year
B. Use a query with criteria based on the year in the date field
C. Rename your sales table each year
D. Set up a report to only show data by the current year
Q3. What is the recommended approach if your Access database has gotten very large due to many years of data?
A. Delete old records to create more space
B. Archive older records into a separate archive table or file
C. Start a new database for the new year
D. Keep all data permanently in one table without archiving
Q4. Why is it a bad idea to have fields labeled "previous year," "this year," or "next year" in your database?
A. It makes the database run slower
B. It creates ambiguity as the years pass
C. Access will not allow this type of field naming
D. It prevents using queries on the data
Q5. When comparing values such as budgets across different years, what technique does the video suggest using?
A. Parameter queries with user input for both years
B. Crosstab queries comparing the year field
C. Separate tables for each year and field
D. Manual calculations in Excel
Q6. What should you do if you want to view month-over-month or year-over-year comparisons in your database?
A. Use a query that simply sorts by date
B. Use the Year or Month function in crosstab queries
C. Copy data into two separate tables for each period
D. Rely on autonumber fields for comparisons
Q7. What is the main purpose of autonumber fields in Microsoft Access?
A. To provide a unique, internal identifier for each record
B. To reset to "1" every year for new data entry
C. To indicate the order in which data was entered each year
D. To create user-friendly, sequential order numbers
Q8. If you require an order counter that resets each year, which approach is advised?
A. Use the autonumber field and reset it annually
B. Use a custom sequential counter based on the year
C. Use the current record count from the table
D. Manually enter order numbers at the start of the year
Q9. When might it be acceptable to create separate tables for different years in your Access database?
A. Whenever you prefer for easier organization
B. Only when you are specifically archiving data you rarely need
C. For every major table in the database
D. When using date fields is too complicated
Q10. What should you do if you have to make yearly changes due to year-dependent structures in your database?
A. Consider restructuring your database to use flexible date or year fields
B. Stick with your current design and keep updating manually each year
C. Add new "year" tables and adjust queries
D. Create separate databases for every year
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-A; 8-B; 9-B; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone tackles one of the most common questions I get at the start of every year: how do you reset your Microsoft Access database for the new year? Every January, my inbox fills with people running into the same issues, so I figured it was time to revisit the essential advice for keeping your database design healthy and handling the annual transition without any headaches.
If you have built your Access database the right way, there should be no need for an annual reset. Tables containing dates, such as orders, contacts, or inventory, ought to use a proper date field or at least include a year field. This approach lets you select and work with data for any given year using queries, instead of scrambling to change table structures or rename objects when the calendar flips.
Let me outline the four most common scenarios I hear about, along with my recommended solutions. If any sound familiar, it's worth considering an update to your database design.
The first and most frequent problem is having separate tables for each year, like Sales2019, Sales2020, and so on. This setup complicates queries and reporting, because names change every year and you have to modify all your queries and reports to point to the new tables. Once you get into more advanced techniques like VBA, the problems multiply. Databases should not have objects named with years unless you are specifically archiving data. To fix this, all your records should live in a single table, using proper date fields. If you need to see sales from 2020, you do that with a query that filters by date or year. Use the Year function to isolate a specific year and use parameters if you want your query to prompt for which year you want to see.
The second scenario is similar: instead of new tables per year, people copy the entire database and start a new file for the new year. Then, all queries and reports pull data for all years, making it hard to view a specific year unless you keep making new queries and reports. Again, the solution is a single table with criteria in your queries to select the desired year. Now, if the reason you copy the database is that the file has grown too large and is hitting the Access size limit, you might need to split your database or archive old data into separate tables or files. If you find you do not need records from 15 years ago in your daily workflow, you can move those into an archive and keep your working database streamlined. If you ever need that old information, you can still access it in the archive.
The third common mistake is using fields like "previous year," "this year," or "next year" for tracking data such as budgets. This is not ideal because those field names quickly become confusing as time passes. Years from now, what does "previous year" refer to? Instead, your table should include a year or budget year field along with whatever category or item you are tracking. If you need to compare data year over year or month over month, you can use a crosstab query or similar technique to organize and report on data from different periods. While I have a video on crosstab queries and another for month-to-month sales comparison, the same concepts apply for years: just switch from the Month function to the Year function.
The fourth recurring issue people bring up is wanting to reset their autonumbers at the start of a new year. This is never necessary, and you should avoid altering autonumbers. Autonumbers exist for the system's internal usage, not for humans to rely on or reference. They are there to keep records unique. If you prefer having your own sequential numbers that reset at the start of the year, you can build your own custom sequence counter, tied to the year, but this is an advanced task. In this case, you can have something like order numbers that reset annually for each customer, formatted however you like.
If you find yourself making big changes to your database every January just to keep it running smoothly, consider reworking your design; you should not need manual resets if your tables are set up properly in the first place. I am always interested in hearing about unique situations, so let me know if you are encountering year-based problems that don't fit into these categories. I am happy to keep updating my advice and adding new tips as oddities come up.
For additional guidance, I also recommend checking out my videos on year-end reporting and working with fiscal years. The fiscal year seminar I put together covers a variety of topics related to handling fiscal-year data in Access.
That covers the fast tips I wanted to share today about resetting your database for the new year. I hope you all had an enjoyable holiday and a safe start to the new year. Feel free to leave your thoughts or questions in the comments, and I look forward to seeing how this information helps you. If you want the full video tutorial with detailed step-by-step instructions for all the topics discussed here, you can find it on my website at the link below.
Live long and prosper, my friends.
Topic List
Problems with having separate tables for each year
Combining yearly data into a single table
Using date fields to filter records by year
Setting query criteria to display data for a specific year
Creating parameter queries to prompt for the year
Using the Year function to extract year from a date
Problems with creating a new database file each year
Archiving old records into an archive table
Splitting a database when the file size gets too big
Restructuring databases with "previous year" and "this year" fields
Storing year as a field instead of separate yearly columns
Using crosstab queries to compare year over year data
Why not to reset autonumbers for the new year
Creating custom sequential counters that reset by year
Avoiding use of autonumbers for record identification
|
