In this video, learn how to build your sorts and filters into your queries.
- [Instructor] By default, data is in natural order. So, building sorts and filters in our queries can be a big time saver. Let's go take a look at our data. So, we're in our base query, and right now when we run that query, it's in the natural order of the way that it was entered. So, what I want to do is actually go adjust the sorts. For example, I want all of my data to always be sorted by department. So, I'm actually going to move that Department column. I'm just going to lift, click and hold on the column, and drag it to the first position.
And then in the sort, I'll choose Ascending. Because it's in the first position of the grid, it'll be the first thing that's sorted. Now, I don't want to change the location of first and last name, but I do want last name to be sorted before first name, so I'm going to drag last name before first name. And I'll choose ascending for last name, and ascending for first name. So, the query grid is going to sort in the order of the grid. Let's go to the Design view and run this. Alright, perfect.
So, when I look at my data, I really don't want to see Department first. I really want to start with the users, so I'm going to left-click and hold on the department, and drag it to the end. I want to say First Name and then Last Name, so I'll drag First Name over, perfect. Now, let's go look at the Design view, and you'll note that it didn't change. So, the design actually controls the sort order of the data, and then you in the Data Sheet view can then go change that to look any way that you'd like.
Alright, let's go ahead and save that base query, and go back to our Design view. So, we're working with about 1739 in our data set. But for our reporting going forward, we only want to see anything that occurred after June 30th, 2016. So, instead of filtering it out on the back side of Excel, I'm going to go ahead and include that filter in my base query. That way, every time I use my base query, it will include these sorts and that filter. So, I'll go to my Time and Date stamp, I'll click in my Criteria field.
I'm going to go ahead and type a greater than symbol, and I'll type 6/30/2016, and when I press Tab, Access will add the proper syntax for dates. And then I'll go to my Design view, and run my query. I'm now working with 1233 records that are all sorted by department, and then by the user last name, and then first name. We can build sorts and filters in our queries, and it'll be a big time-saver on the data mining aspect that we typically do in Excel.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Understanding the software limitations
- Working with Access tables
- Analyzing table relationships
- Building queries
- Exporting data for Excel
- Linking to Access data from Excel
- Building charts and PivotCharts in Excel
- Creating macros