Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Typically, dates and times stored within your database are very specific. Dates are usually specific to a particular day and times are specific to the minute. When it comes time to query your data by month or year, Access provides a few simple ways to extract just the level of precision that you need from your Date and Time fields. Let's go to Create and we'll take a look at a new query in Design view. We'll add tbl_DirectCustomers, tbl_Orders, and tbl_Products. Let's go ahead and close Show Table. Now, let's say we want to take a look at the orders that came from specific states over a specific reporting period.
We'll add states to our query. We'll add State to our query and we'll add Price from the Products table. Let's turn on the Totals row, because we want to group everything down to the State. We could turn this Group By to a Sum to get a sum total for each state. If we run this, Access will return a list of each state listed, we have 50 represented, and the total price that each State has contributed to our overall bottom line. Now, keep in mind that the SumOfPrice is adding up all transactions of the entire life of our database, which goes back several years.
Let's go back into our Design view and see how we can control this a little bit. In Design view I'll right-click in the third field here, and invoke the Expression Builder. Let's take a look at some functions here. We'll go into Functions > Built-In Functions > Date/Time. Let's take a look at a function called Year. If I double-click to add it to my Expression Builder, we'll see that Year requires one input and that's a Date field. We'll click on that to activate it and then we'll find the Date field from our Orders table. Expand the TwoTrees database, Tables, Orders and double-click on OrderDate.
So now we've got a function that says we want to extract just the Year from the OrderDate table. Let's go ahead and say OK. Now we're going to group by the years that are extracted. If we run this query, we'll see that each state gets every year listed, so we have data from 2005, 2006, 2007, 2008, 2009, and 2010 in our database and each month and the SumOfPrice aggregation is now left at the Year level instead of the lifetime of the database level. Let's change this up and look at it for month. We'll go back into Design view and since we already have this constructed, we can easily change this instead of Year, we can delete Year and write in the word Month.
Now, Access is going to extract just the month from the OrderDate. If I run that, we'll see basically the same thing. We have our months here on the right side, January through December for each state, so Alaska, January through December, and our SomeOfPrice is aggregating to the month level. Now we do have several years represented here. So this is actually aggregating all of the Januaries together for instance, and that equals $539 and then all of the Februaries regardless of which year. So in order to make more sense of this, we might want to aggregate based off the year and month simultaneously.
Let's go ahead and write that in here. Year, open parentheses and then we'll reference our OrderDate field, square bracket, tbl_Orders, closing bracket. That's the table it comes from. I will expand that over a little bit. Next I need our separator, which is the exclamation mark, and then we need to tell it what field within the table, the OrderDate field. We'll input a closing square bracket. Finally, we'll finish our Year function with a closing parenthesis. Now, we've got the year extracted, and the month extracted.
If I run that, we'll see that we now have our data broken out byyear and month for the SumOfPrice aggregated to that level. In order to maintain flexibility it's always best to store database upon the finest level of detail that you could ever conceivably require. Using queries, it's easy enough to strip away all of the fluff based on the specific task that you're working with 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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64651 Viewers
80 Video lessons · 124285 Viewers
52 Video lessons · 60227 Viewers
59 Video lessons · 46062 Viewers