Understand parameter queries

- [Instructor] So far, we've seen lots of ways that you, the query's designer, can control the records returned in the data sheet results. But what if we wanted to set up a framework and allow the end user of our queries to supply some of that input? In other words, how can we create a flexible query that displays information based upon the 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. We can see how this works by starting up a new query in Design view. Now, for this example, I want to explore the information about our employees. So I'll add that table in and close the add table window. Then I'll add in a couple of fields. Let's grab the first name, last name, department, and email address. Now at this point, I don't have any criteria in this query, and if I run it, I'll just get back a single row for every employee. And I can see I have a total of 83 employee. If I go back into Design view, I can start filtering these down based off of the department that the employee works in. For instance, we saw previously that we could add in a department called Marketing. And now when I run the query, I'll just see my marketing employees. Now I could save this query into my navigation pane, and every time I run it, I'll just get back my marketing employees. Then I could create another query by coming down here to the criteria and changing this to something else. How about finance? Now, when I run the query, it's going to return just my finance employees. Again, I could save this version of the query down into the navigation pane and have a second query to explore my different employees based off of their departments. You can probably see the problem with this approach though. You'll need dozens of different queries that are all set up to look at a slightly different department. Instead of doing that, what we can do is change the department criteria to be a little bit more flexible. Let's go back into Design view. Instead of hard coding a specific criteria for our department, we can set up a parameter request. Then when the query is run, it'll ask you for which department you want to see. At that point, you can supply the name of the department that you're currently interested in, and the query will finish up and it'll sort all of the employees based off of that criteria. To see how this works, I'm going to right-click in the criteria box under department and open up our Zoom window. Then I'll get rid of the finance criteria. To set up a parameter request, we'll use square brackets. So I'll open up a bracket here. And then we type in a question that we want to pose to the end user. I'll type in the text, what department do you want to see? Make sure to include the closing square bracket, and then press OK. Now when I run the query, you'll see the different result. You'll get this little popup dialogue box that says what department do you want to see? At this point, we can supply the criteria that we're interested in. Let's say marketing again. When I press enter, the query results display just those employees. Then, I can come back up here to the refresh all button here in the ribbon. Press it, and you'll get a instance of that popup box. Again, asking me for a new department. This time, I'll type in finance, and press enter. And there are my finance employees. So all of this is being done by just a single query with a parameter request. Now, the downside to this parameter popup box is that it doesn't really provide any context for the end user. For instance, I really don't have any idea what departments are valid choices. And the parameter request dialogue box isn't going to help me if I simply misspell something. If I try and type in a department named sales, for example, and press okay, I don't get any results back. This isn't really clear whether I don't have any employees in the sales department, or if there an even a department named sales at all. We can enhance this a little bit by using wild cards and various operators to protect against returning no values like this. Let's go back into Design view. Then, we'll come back down to the criteria and I'll right-click and open up the Zoom window again. We saw that the asterisk wildcard character will return any of individual characters. I can add that into my parameter request by typing in the like keyword, then inside of double quotation marks and asterisk. After that, I'll type in an ampersand followed by the parameter request that we previously typed in. Then at the very end, I'll add in another ampersand and another asterisk in side of double quotations. So what we're doing here is joining this parameter to two asterisk wild card characters. This will effectively search for anything plus whatever we type into the parameter box plus anything. When I say OK to this, you'll see what happens. If I run the query again, it gives me the same popup. If I don't type in anything and say OK, it gives me all of my employees back, no matter what department they're in. Alternatively, if I press the refresh button and type in a parameter, how about customer service, that gets put into the parameter request and I get just those employees. So this parameter request box with the like, and the asterisk wild card characters creates a very flexible system. Let's go ahead and save this query. I'll just press control + S on my keyboard, and I'll call it Employees by Department. That'll save it down into the navigation pane. So including parameter requests in your queries is a great way to increase the 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 in 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 variables in a query however. In the next video, we'll see how we can build upon this basic parameter concept.