Join Adam Wilbert for an in-depth discussion in this video Using other Date/Time functions, part of Access 2007: Queries in Depth.
Typically, dates and time store within your database are going to be very specific. Dates are usually specific to a particular day and times are typically precise to the minute. But when it comes to evaluating or aggregating your data, you might want to look at it a little less granularly. When it comes time to query your data by month and year, Access provides a few simple ways to extract just the level of precision that you need from your date and time fields. So we'll go ahead and create a new query in Design View. I'm going to add our Orders table, and I'll go ahead and close the Show Table window. Now we're going to add two instances of our OrderDate. I'm also going to turn on our Totals row and I'm going to change the first Total to Count.
If I run this query, we're going to get a list of the number of orders that were placed on each day. We can see that some days had multiple orders and some days only had one order. Let's go ahead and go back into our Design View. So if I wanted to organize my database off of a different level of detail, for instance, I want to group everything by a specific month, I can use a function to tell Access to do this. Let's go ahead and right-click on our second OrderDate column and open up the Zoom box. At the very beginning, I am going to type it a new alias and I'm going to call this Grouping. I'm going to use a function for Month. I'm going to wrap our OrderDate field in square brackets to tell Access that this is the data that we're going to be using.
So now we're going to be extracting just the month from the OrderDate. Go ahead and say OK and run our query again. You can see that we get a numeric code that represents the month that we're in. So for instance, in the month of January we sold a total of 199 products. For the month of December we sold 151. Now this is grouping all January's together. So it doesn't matter if it's in January 2005 or January 2010. All the January dates are getting looped together in this view. We can go back into our Design View and do the same thing for year. This time we're going to type it directly into the field here. So I'll expand this open a little bit.
We're going to put a new alias here. We're going to call this one Group by Year. We're going to use the Year function. I'm going to pull the year out of our OrderDate, finishing with a square bracket, and a closing parenthesis. I'm going to go back to our first one here and specify that this is Group by Month. So we can tell the difference between the two. Now when I run our query, we'll see that now we have all of our January 2005s grouped together. We can see that we have a total of 29 orders in January 05. But let's go ahead and sort this little bit differently.
I'm going to sort the Month from smallest to largest. Then we'll sort the Year from smallest to largest. Now we can get a better look at our data, so we have everything in order from January to December 2005, and then January to December 2006 and so on. So this is how we would use the Month and Year functions to format our dates. We have similar functions for day, so that will be a Day and then parenthesis, or Weekday and then parenthesis. There are also functions that do the same thing for times, for hour, minute, and second, and they will work exactly the same way. So in order to maintain flexibility, it's always best to store data based on the finest level of detail that you can ever conceivably require.
Typically, this is going to be a date specific to the day and a time specific to the minute. Using queries, it's easy enough to strip away all the fluff based upon the specific task that you're working with and when you don't need that level of detail. With these Date and Time functions, Access makes it easy to see exactly what you need and nothing that you don't.
- Naming conventions and best practices
- Working with joins and primary keys
- Using comparison operators
- Printing query results
- Creating parameter queries
- Creating calculated fields
- Using the Expression Builder
- Making conditional statements
- Appending queries
- Updating queries