Hour Minute Second
By Richard Rost
4 years ago
Find Busy Times Using the Hour, Minute Functions
If you capture the date and time when you enter records into a table, you can use the Hour, Minute, and Second functions in Microsoft Access to determine the busiest times of the day.
Members
Members will learn how to calculate busy times to 15 minute intervals.
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!
Pre-Requisites
Links
Recommended Course
 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, #fasttips, just the date, convert date/time to date only, get date from a datetime, datetime field date only
Intro In this video, you will learn how to use the hour, minute, and second functions in Microsoft Access to analyze order times and determine the busiest hours of the day. I'll show you how to extract the hour component from order dates, create aggregate queries to group and count orders by hour, and explain why tracking time data can be valuable for different types of businesses. You'll also see how to display minutes and seconds from your order data using Access queries.Transcript Welcome to another FastTips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, we're going to learn how to determine the busiest times of the day based on your order dates, if they include times, using the hour, minute, and second functions in Microsoft Access.
Yesterday in my date value video, I showed you how you can use the date value function to peel off just the date part of an order date. If you are doing calculations where you want to see orders between two dates, for example, you don't want that time on there. If you want to learn more about that, go watch this video.
This assumes you're using the now function or typing in the time portion as well when you're collecting order information. I do. I want to know what time of the day an order comes in because then we can use that to determine what the busiest times of the day are. That's what we're going to do in this video.
Now, this might not be that important for an internet business because I've got customers all around the planet, so I've got busy times based on different time zones. But if you have a retail establishment, you might want to know when you get most of your sales, for example, between 3 pm and 5 pm, so you can ramp up your staffing accordingly. This is very useful information for a lot of different types of businesses.
If you haven't yet watched my year, month, day video, go watch this first.
The hour, minute, and second functions are almost identical to year, month, and day. They just give you different bits of information. Go watch this. I explained a lot of stuff in this video if you haven't used these functions before.
Also, go watch my aggregate query video because in that video, I teach you how to group stuff together. We want to group together all of the orders that fall in the four o'clock hour, for example, and count them up. To do that, we'll use an aggregate query. So go watch this first, too. These are free videos on my website and on my YouTube channel. Go watch them and then come back. You'll find links down below you can click on.
Here I am once again in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to. I've got my order table here that's got a bunch of order dates in it. My order date also has the time in there.
If you want to get that information in your table, right now I'm using just the date function. But if you use now in here instead of date, it'll get the date and time that the order is placed. If I come in here now, notice the default value now has exactly today's date and time. November 1st at about 5:18 pm.
For order dates and contact dates and customer since dates, I usually track a time if possible because sometimes that information is useful. Sometimes it's not. If you determine later on that you don't need it or don't want it, you can always chop that off with the date value function, which is what I showed you in yesterday's video. It's better to have it and not need it than to need it and not have it.
So what we're going to do in this video is we're going to look at this guy. We're going to chop off just the hour. We're going to say what hours of the day are the busiest? The four o'clock hour, the five o'clock hour, and so on. In other words, during what hour of the day do most of my orders come in? It doesn't depend at all on the date. You just care about the time, the hour.
I was going to use the time value function to do this, but I'm going to save that for another video because I realized as I was putting this together, all we really need is the hour function, which is much, much simpler. Let me show you how this works.
Let's go and create a query, create a query design. Bring in that hour table, close that, and bring in the order date. Right here, we're going to put a calculated field and figure out what that hour is from the order date. Zoom in so you can see it (Shift+F2). We'll call it H. That's my hour, which is going to be the hour of order date. That's it. That's all you have to do. That will give us what hour of the day that order date falls in.
If I run that now, there you go. Yes, it's 24-hour time. For most mathematical calculations and stuff like this, you want the 24-hour time, because you can always convert it back to something else like 5 pm later if you want to for reporting. For your calculations, trust me, stick with the 24-hour time format.
Let's save this. I'm going to save this as my order hour query. It's easiest to do this stuff if you break it up into multiple steps, multiple queries. I see a lot of people trying to jam all this stuff into one query and they have problems. Get one piece of information at a time. Then later on, if you want to go back and minimize or reduce the number of queries that you have, you can do that. But to get it working, just use multiple queries. It's easier.
Let's close that. Let's create another query and bring in the results from that one, order hour query. I don't want the order date. I don't care about it anymore. Don't need it. All I care about is that hour field. Bring that in, hit run. There you go.
What I essentially want to do is count these up. How many 17s are there? How many 21s are there? How many 16s are there? That's 4 pm. You can see just by looking at 1, 2, 3, 4. There are four 16s in there. That's probably our winner.
We'll use an aggregate query to do this. Turn on totals here and have group by. Then run that. That groups them all together so you have only unique values. In the next column, I want to see a count of that same value. I'm going to bring in the H again and change the function here to count. That'll give me a count of them. This tells me what the grouping is and this one will tell me what that count is. Then run. There you go.
You can see right there, I've got four 16s, two 15s, and a bunch of ones. If you want to sort this, you can sort this based on this column as well, sort descending. That'll give you the most popular ones up top. You can see right there, four o'clock and three o'clock are my most popular times.
Let's save that as order hour total query.
What if you want to see the minutes and the seconds on here? In design view, same thing. The minute function will make that M. Actually, I'm going to make it N because in Access, M is mod if you're looking at the format function, and this confuses a lot of people. M is for mod, N as in Nancy is for minute. So let's make that the minute of order date. S is going to be the second of order date. That properly pulls that thing all apart into its parts. If you change one of these guys to like five seconds, there you can see a five of it, right? Hours and seconds.
If you have these components, you can reassemble a date time with the date serial function and time serial function, but that's a whole different video.
What if you want to break this down? Instead of just whole hours, you want to see hours and minutes in 15-minute intervals. Like, you want to know that four o'clock to four fifteen is a busy time, maybe something like this, where we break up the minutes into 15-minute intervals. Let me assemble that back into a time, and then we can use that time for our aggregate.
I'll show you this in the extended cut. Did you hear that - extended cut for a FastTips video? Yes. This is my first one. I've never done an extended cut for a FastTips video. Usually, I just do the extended cuts for the TechHelp videos. But these are my videos. I can do whatever I want.
So if you want to learn how to do this in 15-minute intervals, check out the extended cut for the members. Remember, Silver Members and up get access to all of my extended cut videos. There are hundreds of them for these and my TechHelp videos. Check them out.Quiz Q1. What is the main goal of the video?
A. To learn how to group order data by month and year
B. To find the busiest times of the day using hour, minute, and second functions in Access
C. To export order data to Excel
D. To create a user login system for ordering
Q2. Why might tracking the time of order placement be especially useful for retail businesses?
A. To calculate international shipping times
B. To adjust staffing based on busiest sales periods
C. To change prices based on time of day
D. To speed up inventory turnover
Q3. Which function is used in Access to extract the hour from a date/time field?
A. MINUTE
B. DATEVALUE
C. HOUR
D. SECOND
Q4. What function should you use to capture both the date and the time when saving an order?
A. DATE
B. TIME
C. NOW
D. DATETIME
Q5. What is the recommended time format for calculations such as finding the busiest order hour?
A. 12-hour time format with AM/PM
B. 24-hour time format
C. Epoch time
D. Decimal hour format
Q6. When building a query to get the busiest order hour, what is the process recommended in the video?
A. Do it all in one query for efficiency
B. Break it up into multiple queries for clarity and troubleshooting
C. Only use macros, not queries
D. Focus on manual calculation in reports
Q7. In an aggregate query to count orders by hour, what function do you apply to the hour field to see frequency?
A. Sum
B. Max
C. Count
D. Average
Q8. If you want to see order information broken down by minute and second as well as hour, which functions should you use?
A. YEAR, MONTH, DAY
B. DATE, TIME, NOW
C. HOUR, MINUTE, SECOND
D. SUM, COUNT, AVERAGE
Q9. Why does the video use 'N' instead of 'M' to represent the minute field in Access?
A. 'M' is reserved for month
B. 'N' is the standard abbreviation
C. 'M' is used for the mod function in Access formatting
D. 'M' is a deprecated function in Access
Q10. What must you use if you want to group data into intervals like 15-minute blocks instead of by whole hour?
A. Time serial and date serial functions
B. Manual entry corrections
C. Custom formatting using text fields
D. The video only covers grouping by hour, not intervals
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 9-C; 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 video from Access Learning Zone focuses on analyzing the busiest times of day for your orders by examining the time data included with your order dates. In this lesson, I will show you how to use the Hour, Minute, and Second functions in Microsoft Access to determine exactly when your most frequent orders occur.
If you're using the Now function, or otherwise including the specific time when orders are entered into your database, you can collect valuable information about customer activity. For retail stores in particular, knowing if most of your sales happen in the late afternoon, for instance, can help you manage staffing levels more effectively. Although this might be less relevant for internet businesses with customers in many time zones, a local business can benefit greatly from this insight.
Before getting started, if you are not already familiar with how to extract the date from a date/time value, I think it is helpful to review my previous videos covering the DateValue function and the Year, Month, and Day functions. These serve as useful introductions to the process and show you how similar the Hour, Minute, and Second functions are. Additionally, if you're not familiar with aggregate queries in Access, I recommend viewing my video on that topic since grouping data will be an important part of today's process.
For this tutorial, I am working with my free TechHelp template database which you can download from my website. The order table I will use has order dates with the time portion included. By default, using just the Date function only inserts the date, but if you switch to using Now as the default value, Access will record both the date and the current time whenever a new order is created. I recommend capturing the time along with the date for order dates, contact dates, and customer since dates, as it can be useful down the line. If you decide you don't need it, you can always remove the time information later using DateValue, which I covered in a previous lesson.
The heart of today's lesson is analyzing which hours of the day are the busiest for incoming orders, regardless of the specific date. We only care about the hour to see patterns in order volume through different times of day. Although I initially considered using the TimeValue function, the Hour function actually does the job in a much simpler way.
The process begins by creating a new query. After adding your orders table, create a calculated field to extract the hour of the order date using the Hour function. This will generate a 24-hour value which is what you want for calculations. Sticking to the 24-hour time format simplifies the analysis, and you can always convert it to a 12-hour format for reporting later.
Once the query is saved, I typically suggest breaking up your work into multiple queries rather than trying to do everything at once. This makes the design easier to follow and troubleshoot. Pull the hour field from your previous query into a new query, and use an aggregate query to group and count the total number of orders for each hour. Sorting the results in descending order will show you at a glance which hours are the busiest.
If you also want to see the breakdown by minute or second, the Minute and Second functions work similarly. In Access, because 'M' is typically reserved for 'mod' in other functions, I prefer to use 'N' in calculated fields for minutes to avoid confusion. You can pull apart your date/time fields into hours, minutes, and seconds with these functions if you need a more detailed analysis.
If you want to go a step further and analyze order volumes in segments smaller than an hour, such as 15-minute intervals, this can also be done. For example, you could combine the hour and the nearest 15-minute block to see which short time frames are the busiest. I am going to cover exactly how to do this in the extended cut of today's video. This is actually my first ever extended cut for a FastTips video, often reserved for TechHelp videos, but I wanted to provide this extra material for those interested.
Silver Members and higher have access to the extended cut, as well as hundreds of other extended lessons available for FastTips and TechHelp videos.
For the complete video tutorial, which walks you through each step in detail, be sure to visit my website at the link below.
Live long and prosper, my friends.Topic List Using the hour function to extract hour from order date
Creating a query to show the hour of each order
Saving and organizing queries for step-by-step analysis
Counting orders per hour with aggregate queries
Grouping order data by hour in a totals query
Sorting order hour results to find busiest times
Extracting minute and second from date values using minute and second functions
Naming conventions for minute and second values in Access queries |
