From the course: Access 2019: Building Dashboards for Excel

Enhance queries

From the course: Access 2019: Building Dashboards for Excel

Start my 1-month free trial

Enhance queries

“

- [Instructor] So when you've been using Excel for reporting, then you've probably learned how to build a lot of functions. When you start leveraging access, you want to build those functions into your Queries so that that data is already available to you in Excel when you start building your dashboards. Let's take a look. OK, so we're in our Base Query, and you notice that first name and last name, there's a lot of space, this is actually called a river of white space. It's because all the first names end in different places, but all the last names all start in the same place. So when we report on this, it's a best practice to combine them or what you would do in Excel is concatenate, so let's change our view, and then in our first blank column, we're going to right-click and choose Build. OK I want to display it as first name, space, last name, so I'll double-click First Name, I'll add an ampersand, and because I want a space to show up I'll do open quote, space, close quote, I'll do another ampersand, and then bring in Last Name. Perfect, I'll go ahead and click OK, and then notice that it gives me that Expression 1, I'll change this to UserDisplayName, and then let's go run it. You can immediately see some space saving, and it's actually more readable. Alright, let's do the same thing for Manager. I'll change my view, go to that first blank column and choose Build, and then I'll size this so I can see my Builds a little bit better, I'm grabbing that corner there, I'll double-click on Manager and Departments First Name, ampersand, quote, space, quote, another ampersand, and then I'll do Last Name. Alright, I'll click OK. And this will be called ManagerDisplay. OK, I got my ManagerDisplay name and let's go run it just to make sure there are no problems, perfect. OK, so we want to be able to see to total seconds represented as minutes, so let's go build that calculation in as well. I'll change my view, going to right-click and choose Build, and I'll take my Total Seconds and divide them by 60, and this will give me my minutes. Now if I know I'm going to call this Minutes, I can go ahead and give it its column heading here, let's do MinutesWatched, perfect, OK, and then let's go run that. So immediately I see pound symbols, and that means it can't show all of the number, so I'll double-click, and I see what's happening here, is it's dividing and it's giving me that decimal place. OK, so one of the very first things we're going to do is try to adjust the properties. So I'll change my view, I'll go to MinutesWatched, and choose Properties. I'll go ahead and make it a General Number, I'll do zero Decimal Places, and we'll go see if that fixed it. I'll run it, I still see my decimal places. OK, you will face little data challenges like this no matter what if you work with data, so one of the easiest ways to solve this is to use the Round function. Let me go change my view, I'll close that Properties sheet, I'm going to right-click my new MinutesWatched and go back to Build, and then I'm going to wrap the Round function, going to round it, don't need any precision, just go with the defaults, and I can click OK. Tab out of it and now we'll go run it, see if that fixed us, perfect. Alright, awesome, and it uses the standard defaults of if it's below five, it rounds down, if it's above five, it rounds up. Now we built-in calculations into our Query, so that when we bring this to Excel, it automatically has those functions built-in, so that we don't have to rebuild them on the other side. Any calculation you can add to your Query, I would encourage you to do that.

Contents