Tip:
Highlight text to annotate it
X
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.