To prepare for this tutorial, download our University Records database, and open a new query, by clicking on the "Create" tab, and then "Query Design."
- Add the Students table.
- Add these fields to the query: first_name, last_name, and birthday.
- Sort birthday ascending.
Here's the problem: When you run this query, it sorts first by year, then by month, then by day. Sorting like that makes sense because obviously December 25, 1953 came before October 31, 1975. However, we want our birthday list sorted by month, then day, and then year. That way we can send out birthday cards to everyone that was born in June, regardless of the year they were born.Query Expressions
Were going to use some functions to pull out important information from our birthday field.
- In query design view, clear the sort from the birthday field.
- In the next column over, name a new field by typing (without quotes): "B-Day Month:"
- Now type in the function: "Month()"
- Now, between the parenthesis, we want to specify what value we want the month of, and that would be the birthday. So type: "[birthday]" between the parenthesis.
- Sort this field ascending, and run your query. (final design view below)
To sort next, by day, add another column on your design view. Guess what the name of this function is...Day()!
- It should look something like this: "B-day Day: Day([birthday])"
- Sort ascending.
- Done
One response to “Sort Birthdays by Month, then Day, then by Year”
Okay, so here is a bonus to this post. First of all, you don't want to see the columns that you created, so uncheck their Show box.
Now, there is a function called MonthName. It can only be given a number, not a whole date, so we have to put a function within a function.
It'll look like this:
B-day Month: MonthName(Month([birthday]))
I promise it will work. Try it!
Leave a Reply