Fiscal Sort
By Richard Rost
8 months ago
How to Sort Dates by Fiscal Quarter in Microsoft Access
In this Microsoft Access tutorial I will show you how to sort dates by fiscal quarter when your fiscal year does not start in January, using queries and calculated fields to shift your data for accurate fiscal reporting. We will cover calculating the fiscal quarter from any start month, adjusting order for reports, sorting issues in queries, and applying the technique with both a step-by-step method and a quick formula.
Desmond from Sarasota, Florida (a Platinum Member) asks: How can I sort the data in my table based on our fiscal year instead of the regular calendar year? Our fiscal year starts in April, so I need April to come first and March to come last. I want to make sure my reports list everything in proper fiscal order.
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, sort dates by fiscal quarter, fiscal year starting in April, shift fiscal quarters, query for fiscal order, calculated fields, DatePart function, sorting by fiscal month, sorting by fiscal day, fiscal quarter formula, integer division, modulus, aggregate queries, report grouping
Subscribe to Fiscal Sort
Get notifications when this page is updated
Intro In this video, we will learn how to sort dates by fiscal quarter in Microsoft Access, even when the fiscal year starts in a month other than January. I will show you step-by-step how to calculate fiscal quarters using queries, adjust quarter values to align with a custom fiscal year start, use functions like DatePart and IIf, and create calculated fields for proper sorting. We will also cover handling null values, working with aliases, and applying a formula to compute fiscal quarters for any starting month.Transcript Today we are going to see how to sort dates by fiscal quarter. A lot of companies start their fiscal year in a quarter that does not begin with January. So I am going to show you how to shift all those quarters over and sort your data accordingly.
Today's question comes from Desmond in Sarasota, Florida, not too far from me. One of my Platinum members. Desmond says: How can I sort the data in my table based on our fiscal year instead of the regular calendar year? Our fiscal year starts in April. So I need April to come first and March to come last. I want to make sure my reports list everything in proper fiscal order.
So this basically is going to involve calculating your fiscal start quarter and assigning that value of one. Then we can just add on the month and the date after that. Sorting from that point is easy. I am going to show you two methods. One, I am going to walk you through it using basic calculations and a query, which makes it easier to understand. Then once you understand it, I am going to show you a quick formula you can use. If I just show you the formula right now, you are not going to get any appreciation for what it is or why it works. So let me explain it the long way first.
This is for all the people who complain in the comments and say, just get to it already instead of all this stuff. The stuff is what makes you understand it. If you just want the answer, there it is. There you go. Have fun.
Now, for the rest of you who want to actually understand this, I am going to consider this an expert level video. What does that mean? Well, it is a little bit beyond the basics. We need some prerequisites first. We do not need any VBA coding for it. So it is not quite developer level. I like expert as that sandwich between beginner and developer.
You should know how to work with calculated fields and queries. You should know how to use the If function in a query. You should know how to use the Year, Month, and Day functions. Specifically today, we are going to use Month and Day. You should know what Null values are. You should know what an alias is in queries. You should know what the DatePart function is.
If you do not know any of this stuff, go watch these prerequisite videos first. They are all free. They are on my YouTube channel. They are on my website. You will find links to them in the description down below. Go watch those and then come on back.
So here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. In here, we need some data to work with. I have got a bunch of customers in here. The customers have dates on them. You have got a CustomerSince field. We are just going to pretend that is our order date, and the credit limit here is the order total. We are just going to pretend, it does not really matter.
Now, obviously the first thing you are going to want to do is limit your dates. You are going to set up a query and get the values between two dates. So if you want fiscal year 2024, for example, and your fiscal year starts in April, you would set up a query where you can type in the start date and the end date: April 1, 2024 through March 31, 2025. That is all simple to do. I have got another video that explains exactly how to do that. Go watch this if you do not know how to set up a query showing you orders between two dates, for example.
We are just going to assume you want to sort all of these dates based on their fiscal month and day. The fiscal quarter, month, and day.
So the first thing we have to do is convert the actual quarter over to the new quarter. Let us create a query. I am going to bring in my CustomerP, and I am going to bring in from that the CustomerSince field, which is our date. I am just going to alias this and call it D. That is D: just like that.
Oh wait, I have a new trick here. Wait, I will turn the zoom on. I am using the Windows magnifier. There it is. I am calling CustomerSince. Just like that. Just creating an alias.
That alias just makes it easier to type everything else in. Sometimes I have to use the Windows magnifier because Access does not have built-in zooming like Excel and Word, because Access is literally the red-headed stepchild of Microsoft Office.
Next up, we can figure out what the quarter is - the actual quarter of that date. Again, I will zoom in here. There we go. We have Q as the DatePart of, and we are going to go Q for our format. That is inside double quotes, and then our date field is D, just like that.
Now, when I run the query, I get the actual quarter of this date here, forget the year. Remember our previous query is going to get all those years, so we are just going to pick the dates that you need. This is quarter four, quarter one, quarter two. This all looks correct.
Now we have to shift that quarter over based on when your actual fiscal year starts. In this particular case, their fiscal year starts in April, so we are going to subtract one from everything. Quarter two becomes quarter one. Quarter three becomes quarter two. Quarter four becomes quarter three. Quarter one becomes quarter zero. We will deal with that in just a minute.
Let us come into Design View.
Now, we are going to make another field. Do you like the Windows magnifier better or do you like it better when I Shift+F2 zoom in? Because I can use the Access Zoom box. This is what I have been using up till now. I just decided to try using the Windows zoom.
Now we are going to shift over our quarter. So, NewQ is going to be the regular Q minus one. We figured out regular Q. Now if I run this, I have our regular Q, but zero should not be there. We want to move zero to the end.
So what we are going to do is say, if this turns out to be zero, let us make it a four. We can do an If function in here. We can say, if Q equals one, then make this a four, otherwise make it Q minus one. Now, when I run it, I get that. Four is a three, one is a four, two is a one and so on.
We are getting there.
I cannot really decide which one I like better. With the Windows magnifier you can still see it, but the resolution is a little off. The other zoom has the big lettering. Tell me in the comments which one you prefer.
Now we have our new quarter. We are also going to need the month and the day so we can sort all these dates properly. We have the new quarter; now we need the month and the day. So, M - I will zoom in - M is the month of our date. Then here, D - but we cannot use D again, we have to use DY maybe. Our day is going to be the day of D. Let us make this MO, to stay consistent.
Now, if I run this, there we go. There is our date, or the quarter of that date, the new quarter, the month, and the day. All I have to do is sort based on this first, this second, this third, so you get all the ones first, then the month and the day after that.
Let us save this query first. I am going to save this (Ctrl+S) as my FiscalQ. Now, let us try putting a sort on this, and you are going to see what happens. We want to sort based on this, then by this, then by this. When I run it, I get Enter Parameter Value. What does that mean? I have a whole separate video on this too. I will put a link down below.
Essentially, it does not know what Q is at this point while it is trying to sort because it is aliased over here. You have a couple of options: you can either use this whole calculation in here in place of Q, or just feed this data into a separate query, a second query. I like to do it that way because it is easier to deal with.
So we have FiscalQ, save that, just make another query. Yes, I know this ends up adding lots of queries over here in your navigation pane, but trust me, it is easier to deal with.
Bring in your FiscalQ. Now you can sort these values. There is your D, you can bring in NewQ, month, day, and now we will sort these. Then run it. There you go.
Oh, we forgot our null values, that is why I wanted to learn nulls earlier too. You could put it right here - criteria is not null. There you go. Now all of these order dates, again, ignore the year, forget about the year. In fact, what you can do is come in here and say, OrderDate, and you could say, let me zoom in, you could cheat and say Format(D,"mm,dd") like that. There you go. Now it looks like that thing.
Oh, okay, because we have got that format in there, the is not null is not going to work anymore. There are lots of little weird things, but let us just go back to OrderDate, let us go back to D. You could put that is not null in the first query and that would solve that problem.
Now I hope you understand exactly how we got to this point. We figured out the quarter of this, we just shifted it, subtracted one, and then we are using a function. If it turned out to be zero, we push it around to four. And then the month and the day. If you sort based on this, you get everything in the right order. All of these should be in the right order for the month and the day in proper fiscal order.
See? We do not have any Aprils in there.
Now, there is an easy way. Let us save this, let us call this Fiscal2Q, I will leave this in the database for the Gold members. There is an easier way to go through less work if you had that function that I gave you earlier.
This guy, it is not quite as easy to understand by looking at it, and I am honestly not going to walk through all of it. It basically takes the month of your date, subtracts the start month - because the method I showed you a moment ago only works for whole quarters - this will work for any month. So if your fiscal year starts in February, you put a two here, add 12, integer divide by three, mod four, and then add one to it. This mathematically will create the fiscal quarter for you.
So for example, create, go to Design, bring in my CustomerT, bring in that CustomerSince, call it D, and here we will put the calculation. I am going to copy it from my thing here. Copy, paste. Let us say our fiscal year starts in - we will do the same example - fiscal year starts in April. Hit OK and then run it, and we should get the same results.
Now, that you are using a single formula here, you can actually sort by that. Oh, maybe not. Oh, because I have D aliased, that is why. If you just get rid of that alias and just use CustomerSince in here, now you can simply sort it in the same query.And you can use your "is not null" right there. And there you go. That's with the single formula now.
If you want to change what your start month is, let's say your fiscal year starts in February, just put a two there. All your February dates show up first.
It's like math and stuff. I've got whole separate videos on integer division and modulus and how those work. I'll link to those down below. It's actually the same video, my "Otter Even" video, which covers both integer division and modulus or modulo.
This is all five years old. Those are some of my first TechHelp videos. It's so old I didn't even make a funny screen for it, like I do now with the screenshot and stuff on it. Go watch it and enjoy.
If you work with fiscal calculations a lot, I've got a whole seminar dedicated to fiscal year calculations. There is all kinds of stuff in here. We'll do all the sales total stuff, setting it up between dates, aggregate queries, report grouping, and fiscal year is not based on the first of a month. Some companies do that.
I had a client whose fiscal year started June 15th. Why? I don't know, that's just how they did it. We'll set up a VBA function so you can do it with multiple companies.
Yes, this is from 2012 and 2013, but don't worry about it. None of this has changed; this is all the same stuff. There is a little bit of VBA in it. It's pretty good if you work with fiscal years; definitely check it out.
That's going to do it for today. There's your TechHelp video. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access Experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that "show more" link right there. YouTube is pretty good about hiding that, but it's there - just look for it.
If you have not yet tried my free Access Level 1 course, check it out. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, and all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on - and did I mention it's completely free?
If you like Level 1, Level 2 is just one dollar. That's it. It's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month. Yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses, all of them from every subject. You get one free advanced or developer class every month after finishing the expert series.
You can become a Diamond sponsor and have your name listed on the sponsor page on my website.
That's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com.
I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Sorting dates by fiscal quarter in Access
Setting up a query to filter dates between two ranges
Calculating the calendar quarter using DatePart
Shifting calendar quarters to align with fiscal year start
Using the IIf function to adjust quarter values
Creating calculated fields for month and day
Building queries with multiple calculated fields
Sorting data by fiscal quarter, month, and day
Managing alias names in Access queries
Handling null values in date fields for sorting
Using the Format function to display dates in mm,dd format
Creating a secondary query to resolve sorting with aliases
Applying a formula to calculate fiscal quarter from any fiscal start month
Updating the formula for different fiscal year start months
Using integer division and modulus in fiscal quarter formulas
Sorting records based on calculated fiscal quarter fields
COMMERCIAL:
In today's video, we're going to learn how to sort your dates by fiscal quarter, even if your fiscal year does not start in January. I'll show you how to adjust your quarters based on a custom start month, step through an easy-to-understand method using calculated fields and queries, and then I'll show you a single formula that handles any start month for your fiscal year. We'll talk about using the DatePart function, handling null values, and making sure your reports display in the correct fiscal order. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Why might a company want to sort data by fiscal quarter rather than by the regular calendar year?
A. Fiscal quarters often better match a companys business cycle than calendar quarters
B. Fiscal quarters always align with January to December
C. It is required by Microsoft Access to sort data this way
D. Sorting by fiscal quarter prevents duplicate records
Q2. If a companys fiscal year starts in April, which month would be considered the first month of the fiscal year?
A. January
B. March
C. April
D. December
Q3. What is the purpose of creating a calculated field to shift the fiscal quarters in the tutorial?
A. To ensure that fiscal quarters align with the calendar year
B. To allow sorting of dates starting from the companys fiscal start month
C. To automatically insert the fiscal year into records
D. To mark holidays in the dataset
Q4. In the video, what is the primary reason the query returns an 'Enter Parameter Value' error when trying to sort by an alias in Access?
A. Access cannot read aliases in the sorting phase of a single query
B. The table is missing a primary key
C. The alias is reserved for system use
D. Sorting by alias is not allowed in any database
Q5. According to the video, what is a recommended solution if you want to sort a query using calculated fields with aliases?
A. Use a subquery, or feed that querys results into another query
B. Restart the database and try again
C. Rename all the aliases to lower-case letters
D. Reload the tables from scratch
Q6. Why is handling NULL values important when sorting or reporting dates in Access?
A. NULL values can cause errors or incorrect sorting if not handled
B. NULL values are never shown in queries
C. SQL does not support filtering NULLs
D. NULL values should always be converted to zeros
Q7. What Access function is used to extract the quarter from a date in a query?
A. DateVal
B. DatePart
C. QuarterExtract
D. QTR
Q8. In the tutorial, how does the 'NewQ' calculated field account for the fiscal year starting in a month other than January?
A. By subtracting the fiscal start month from the month and adjusting quarter values
B. By multiplying the month by the current year
C. By ignoring the actual quarter and assigning a random value
D. By sorting alphabetically on the month name
Q9. What is the benefit of using a single formula for fiscal quarter calculation as shown in the second method?
A. It allows you to sort and filter directly in a single query
B. It hides fiscal data from users
C. It automatically generates reports
D. It creates custom color-coded charts
Q10. Which prerequisite concepts are recommended before attempting the method demonstrated in the tutorial?
A. Calculated fields, queries, If function, Year/Month/Day functions, DatePart, aliases, and NULL values
B. VBA programming and macros
C. Creating pivot tables in Excel
D. Installing third-party Access add-ins
Q11. What is the main advantage of using the mathematical method for calculating fiscal quarters, as discussed at the end of the tutorial?
A. It can handle fiscal years that start in any month, not just the start of a quarter
B. It only works if your fiscal year starts in January, April, July, or October
C. It requires fewer queries and manual steps
D. Both A and C
Q12. What does the modulus operator (mod) do in the formula for calculating the fiscal quarter?
A. It finds the remainder after division and helps cycle through quarter numbers correctly
B. It multiplies the fiscal quarter by four
C. It changes months to text values
D. It removes duplicate records
Q13. In the context of the video, what is an alias in a query?
A. A temporary name given to a field or calculation for easier reference
B. An Access macro
C. A type of table relationship
D. A password used for database encryption
Q14. What should you do if your companys fiscal year does NOT start at the beginning of a month, for example June 15th?
A. Use a custom VBA function to handle it
B. Use the built-in DatePart function only
C. Ignore the starting day and use June 1st
D. Only sort by calendar months
Q15. What is the outcome if you sort by the fields: adjusted quarter, month, and day, as described in the video?
A. The data will display in correct fiscal order for meaningful reporting
B. The results will be random
C. The years will be ignored completely
D. All fields will be converted to text
Answers: 1-A; 2-C; 3-B; 4-A; 5-A; 6-A; 7-B; 8-A; 9-A; 10-A; 11-D; 12-A; 13-A; 14-A; 15-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 is all about sorting dates by fiscal quarter. Many companies operate on a fiscal year that does not begin in January, so it becomes necessary to adjust how quarters are calculated and sorted in your data to reflect the fiscal year rather than the calendar year.
Desmond reached out with a question about this topic. His company starts its fiscal year in April, so he wants his reports sorted such that April comes first and March comes last, ensuring everything appears in proper fiscal order. This is a very common scenario, and it requires you to adjust the way quarters are assigned to each date.
The process starts by designating the fiscal start quarter as one, then adding in the month and date as subsequent sort criteria. From there, sorting is straightforward. I am going to demonstrate two different methods. First, I will take you through the calculation steps using basic queries so you can follow the underlying logic. Once you understand how the calculation works, I will share a quick formula you can use to save time. It is important to see the process step-by-step before jumping straight to the final formula, otherwise the logic behind it may not make sense.
If you are one of those people who just wants the answer, feel free to jump right to the formula. However, for everybody else who wants a deeper understanding, this tutorial is at the expert level – not beginner, but not quite developer level either, since no VBA is needed. You should already be familiar with calculated fields in queries, how to use the If function, how to use the Year, Month, and Day functions (we will specifically use Month and Day today), understand what Null values are, know how aliases work in queries, and what the DatePart function does. If any of these concepts are unfamiliar, I have prerequisite videos covering them on my website and YouTube channel. I recommend watching those before continuing with this tutorial.
To demonstrate, I am working inside my free TechHelp template database. If you want, you can download a copy from my website. For this example, I am using a table of customers with a field indicating the date the customer joined. We can treat this as an order date for our purposes. The process would be the same if you were working with actual sales dates.
Often, the first step is to limit your data to only those records falling within a specific fiscal year. For instance, if the fiscal year starts in April, fiscal year 2024 would span from April 1, 2024 to March 31, 2025. Setting up a query to filter records between two dates is straightforward, and I have a separate video explaining that in detail. Here, I'm going to focus on sorting a complete list of dates into their fiscal order.
The first calculation we need to make is to convert the standard calendar quarter into the fiscal quarter. To do this, we create a query and pull in the relevant date field. I like to give it an alias (such as D) to make the subsequent calculations easier.
Next, I use the DatePart function to determine the calendar quarter for each date. At this stage, the results reflect the standard calendar quarters. But since the fiscal year in this scenario starts in April, we need to shift the quarters. Specifically, we subtract one from each quarter number. So, quarter two becomes one, quarter three becomes two, quarter four becomes three, and quarter one wraps around to zero. We fix quarter zero by resetting it to four using an If function (sometimes referred to as IIf in Access). The logic is, if the original quarter is one, then after subtracting one, set it to four; otherwise, just subtract one.
With this step done, you now have a new fiscal quarter field for each record. The next part is to extract the month and day from each date, so you can further sort the records within the same quarter. I add two more fields to the query: one for the month and one for the day, each calculated with the appropriate function.
At this point, you have three fields for sorting: fiscal quarter, month, and day. When you sort your query by those three fields, the records will appear in the correct fiscal order, starting with the appropriate quarter and showing all months and days in the proper sequence.
There is one quirk with Access you need to be aware of. If you try to sort using an alias (such as the calculated quarter field) in the same query, Access may prompt you for a parameter because it is not processed in the correct order. The best workaround is to save the first query and then create a second query based on it, where you perform the sorting. This also gives you the opportunity to filter out Null values at this stage, which is especially useful for avoiding empty records.
The alternate approach, which is less transparent but much quicker, uses a more compact formula to determine the fiscal quarter. This formula takes the month of the record, subtracts the start month of your fiscal year, adds 12 (to ensure you do not get negative numbers), and then uses integer division and modulus operations to correctly assign the fiscal quarter. The advantage of this formula is that it works for any starting month, not just those aligned with full quarters. If your fiscal year starts in February, simply replace the start month in the formula with 2 and your February dates will appear at the start of your fiscal year.
If you want a deeper understanding of how integer division and modulus work, I have separate videos dedicated to those functions. I encourage anyone interested to watch them for more details.
For those of you who work regularly with fiscal years, I have an entire seminar available that covers these calculations in much more detail. This includes handling sales totals, grouping reports, working with fiscal years that do not start at the beginning of a month, and even dealing with scenarios where multiple companies have different fiscal calendars. The seminar does include some VBA for more advanced needs.
I hope today's tutorial has given you a clear understanding of how to sort your data by fiscal quarter according to your company's fiscal year, ensuring your reports display information in the correct order.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Sorting dates by fiscal quarter in Access
Setting up a query to filter dates between two ranges
Calculating the calendar quarter using DatePart
Shifting calendar quarters to align with fiscal year start
Using the IIf function to adjust quarter values
Creating calculated fields for month and day
Building queries with multiple calculated fields
Sorting data by fiscal quarter, month, and day
Managing alias names in Access queries
Handling null values in date fields for sorting
Using the Format function to display dates in mm,dd format
Creating a secondary query to resolve sorting with aliases
Applying a formula to calculate fiscal quarter from any fiscal start month
Updating the formula for different fiscal year start months
Using integer division and modulus in fiscal quarter formulas
Sorting records based on calculated fiscal quarter fields |
