Understand parameter queries

- [Voiceover] 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 can we create a flexible query that displays information based on their question and not just ours? The answer is what Access calls parameter queries. 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 our employees and the departments that they're assigned to by creating a new query here in design view. We'll add in our employees table, and then from there, I'll choose the first name, last name, and department fields, and just double click on them to add them to the design grid down below. Now previously, we've seen this criteria row here, and if I come over here under department I could type in a hard coded criteria. For instance, if I type in the word marketing, and press enter, now if I run the query, Access is going to show me all of the employees that work in the marketing department. Now I could go ahead and save this query over here into the navigation pane, and then change it up to say quality control, and save another copy, but you can see that we'll quickly run into a situation where we have lots of queries here that all return essentially the same thing. Just a list of employees based off of the different departments that they work in. Instead, let's take a different approach. We can use a parameter request by coming down here and getting rid of this first criteria that I'd just typed in, and instead of quotation marks, I'm gonna use an opening square bracket. Next, I'm gonna type in some words here that'll describe to my end user what piece of information I'm looking for. I'm gonna type in, enter a department. I'll finish it off with a closing square bracket. This is how you put in a parameter request. Just wrap the request inside of square brackets. And now when I run the query, Access is gonna give me this enter parameter value dialogue box. You can see it gives me the text right here that I typed in, enter a department. Now I can go ahead and type in a department that I'm interested in, let's say marketing, we'll go ahead and say okay, and Access runs that query, and shows me all the employees that work in the marketing department. And if I look at the very bottom I can see that we have 51 employees. If I come up to the ribbon here and press the refresh button, it's gonna prompt me again for a new department. This time I'll type in quality control. We'll go ahead and say okay to that, and the query refreshes to show me the 94 employees that work in the quality control department. Let's go ahead and save this query, I'll press the save icon on the quick Access toolbar, and type in the name employee by department. I'll press okay, and then Access adds that into my navigation pane right down here. Now let me show you a few additional tricks that we can apply using our parameter requests. Let's go back into design view here, and I'm gonna make this field just a little bit wider. One of the things that we can do is combine this parameter request with some wild card characters. Now if I were just to run this query here, and when I get this prompt, not enter anything, if I were to say okay to that, Access doesn't return any records. I might want a situation where if I don't type in anything, the query just returns all of my employees regardless of what department they're in. We can do that by using the wild card character with the asterisk. Let's come here to the very beginning and type in the like keyword, followed by an asterisk, wrapped inside of quotation marks. And then I'll type in the ampersand character which is shift seven on the standard U.S. keyboard, and that'll join this asterisk wild card character to our parameter request. Let's do the same to the very end. I'll add another ampersand, and then another asterisk wrapped inside of quotation marks. Now when I enter my parameter request it's gonna wrap it with these asterisk wild card characters. Let's go ahead and see what happens. If I don't enter anything now, I'll just say okay, leave it blank, it's gonna return all of my employees. If I try and run it again, and type in marketing, it works just like it did before. Let's go ahead and refresh it and just type in something like quality. I'll press okay to this and it returns both my quality control employees, and my quality assurance department employees. Let's go back into design view and show you a couple of other tricks. I can also use a comparison operator to compare numerical values. Let's go ahead and add in our salary information, and down here in the criteria, I'm gonna type in a less than, and then a parameter request to enter the maximum salary. So what we're gonna do here is prompt the end user for a maximum salary, and then we'll return all of the employees that have a salary that is less than that value. And we could do the same thing with a date range. Let's go ahead and add in the hire date field, and I'll come down over here. I'm running out of room, so I'm just gonna right-click and say zoom instead. And let's change the font size while we're here. For this parameter request, I wanna find all of the employees that have been hired within a specific date range. I'll use the between keyword, followed by a parameter request for the starting date, then the and keyword, followed by a second parameter request for the ending date. I'll say okay to this, and now let's run this query. I'm gonna get a couple of parameter requests here, the first one says enter a department, so let's go ahead and say marketing. Go ahead and say okay to that, and it gets me my next parameter value, enter a maximum salary. Let's say 50,000. Say okay to that, I get my third one, enter a starting date, and I want to find all the employees that were hired in 2015. So I'll say the starting date is 1/1/2015, say okay, and enter an ending date, we'll say 12/31/2015. Now Access has all the parameters that it needs, and when I press okay, I find the single employee that meets all those criteria. They work in the marketing department, they make less than 50,000, and they were hired in 2015. Now 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 isn't gonna help me if I simply misspell something. 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 answers to lots of different questions. The interparameter value dialogue box isn't the only way for your end users to define the variables in a query however. In another movie, we'll take a look at how you can use data entry forms and buttons to hook into the variables we've just seen.