Understand parameter queries

- [Instructor] So far we've seen lots of ways that you, the query designer, can control the records displayed in the query data sheet. But what if we wanted to set up a framework, and allow the end user of our query to supply some of the input? In other words, how could we create a flexible query that displays information based off of their question, and not just ours? The answer is what Access calls a parameter query. A parameter query allows you to create a query framework that will request some little bit of additional information every time it's viewed. For instance, instead of simply displaying a list of employees in the marketing department, you can create a query that asks the end user which department, whether marketing, sales, account management or any other. This saves you from creating a separate query for each and every possible question that you or your end users might have. Let's take a look at how parameter queries work by creating a new query in design view. For this query, I just want to take a look at my employees' data, so I'll add that table in, and then I'll add the first name, last name, and department columns. Now previously, we've come down here to the criteria and we've hardcoded all of our criterias right into the query design. For instance, if I said manufacturing, every time I run this query, it would return only the employees in the manufacturing department. I can also change this to say something like quality control, and now every time I run this query, it will return just my employees in the quality control department. Now you can see the trap that we're falling into here, and that's creating multiple queries that all basically do the same thing. Instead what we can do is create a parameter request right here in the criteria box. To do this, instead of hardcoding a criteria, we'll start with an opening square bracket. Then we'll type in a prompt, so that the user will know what piece of information we're looking for. For instance, I'll say, enter a department. And I'll finish that with a closing square bracket. Now when I go ahead and run this query, we'll get this enter parameter value dialogue box where we can enter in a department that we're interested in looking at. In this case, I'll type in manufacturing. When I say okay, it runs, and I can see a total of 151 employees in the manufacturing department. Now if I were to close the query and re-run it, or come up here and press the refresh button on the records section, we'll get the same dialogue box that says enter a department, and I can type in something different, for instance, quality control. When I say okay to this, we'll see the 94 people that work in the quality control department. So that's the basics of how a parameter request works, but we can also mix in some of the other things we've seen with queries here in the parameter request criteria. For instance, we can incorporate wild card characters. If I go ahead and run this query, and when I get the enter a department parameter value request dialogue box and I don't type in anything, and say okay, you'll notice I don't get any records. It might be better to actually get a listing of all my employees if I don't supply a department. To do that, I'll go back into design view, and we'll come over here to the criteria and I'm actually going to open up the zoom box so that I have some more room to type. Then I'll come here to the very beginning, and if you remember with our wild card characters, it all started with the keyword like. Then I can add an asterisk to this parameter request by putting in a double quote, an asterisk and a double quote, and then an ampersand character. The ampersand is a text concatenation character, and it's essentially going to join the asterisk to whatever we fill in here for the department parameter. I'm going to come here to the very end and we'll do the same thing with an ampersand, a double quote, an asterisk, and a double quote, to add another asterisk to the end. I'll go ahead and say okay, and that updates my criteria down below. Now if I run this query, and don't enter in anything, it returns all of my employees. I can also go ahead and refresh this query, and just type in something like quality, and now it returns all the employees that work in both the quality assurance department and the quality control department. Let's go back into design view, and we'll see that we can actually incorporate multiple parameter requests in a single query. Let's add a couple more columns here to our query. I'm going to scroll down here and grab the salary information, as well as the hire date. Then if I want to look up employees based off of their maximum salary, I could type in the less than or equal to symbol and then a second parameter request, so it'll open up another square bracket, and I'll type in enter maximum salary. I'll finish that with a closing bracket, and press enter to enter that in. So there is the second parameter request for salary, here is the first one, for the department; let's take a look at one for hire date. This time I'm running out of room here, so I'm going to go into the zoom box again, and once again I'll just change my font so we can read it. If I only want to focus on people from my specific date range, I can use the between keyword, and then a parameter request to enter the starting date. We'll finish that with a square bracket for the first parameter, then we'll say, and, we'll open up a second parameter request to enter in the ending date. I'll close that with a closing square bracket, and we'll say okay, and that fills in the hire date parameter request. Now we're going to go ahead and run this query, we're going to get multiple boxes here. So the first one says enter a department. Let's say manufacturing. Second one says enter a maximum salary. Let's say 50 000. Next we get to enter a starting date. For this request, I want to focus on all the employees hired in 2016, so I'll say one slash one slash 2016. And my ending date will be 12 31 2016. When I say okay to that, I get all the employees that match those criteria. I can see I have a total of four. They're all in manufacturing department, all make less than 50 000, and were all hired in 2016. Let's go ahead and save this query as employee by department. So you can see how flexible this can be. The downside of parameter boxes is that they provide no real context for the end user. For instance, I have no idea what departments are valid choices and the parameter request dialogue box wouldn't help me if I simply misspell something. But including parameter requests in your queries is a great way to increase flexibility, and give some control to your end users without the need to dive into the query design view. At the same time, parameter queries will reduce some of the single-purpose redundancy that you might otherwise have within your database, and will save you from having to create query after query, that all basically do the same thing, with small variations. By providing the basic framework, you can create a single query that serves up answers to lots of different questions. The enter parameter value dialogue box isn't the only way for your end users to define the variables in the query, however. In another video, we'll take a look at how you can use a data entry form and buttons, to hook into the variables that we've just seen.