Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
I want to start this movie by passing on a fundamental concept that can change the way you think about Access. Every objects, every form, every button or checkbox or label or text box, everything in Access has a value associated within the database. And every name or value can be used as an input somewhere else. Really understanding this concept will set you on the path to becoming a true Access rockstar. And here's why. Access objects, that is the tables, forms, queries, reports, they are all presented by the interface as individual components of your database, but in reality it's the interaction and the interconnections between objects that allow your database to do some pretty amazing things.
In the previous movie we looked at how we can provide our end users with a pop-up dialog box to define some query parameters. Let's take that a step further and see how we could define those same parameters using form controls. I've currently got the frm_SalesRange form opened up from my Chapter 3 custom group. I've had two boxes here that I can enter values in. I'm looking at the total lifetime sales to each customer and I have the option here to enter in two parameters. Let's say I am interested in all of our customers that have spent between $200 and $300 with us.
I could types those values here, 200 and 300, and run our query. The query returns only the customers that we are interested in. So let's see how this is working. Let's go back into our form and we'll switch into Design view. Let's open up the Property sheet by clicking Property sheet on the Tools section of the Design ribbon. We'll switch over to the other tab and if we click on each of these boxes-- I will click on the first one here-- we will see that this box has a name of low. If I click on the second box it has a name of high.
So in our query we can reference these two boxes, the low and the high box on the form of frm_SalesRange. Let's go into our query and use those as inputs for our parameter. We will switch to our query, we will change into Design view, and we will look at the criteria for our lifetime sales field. Now I am going to right-click here and go into the Zoom box. We can see the whole field at once. In the Zoom box we can see that we are referencing the low field of the Sales Range form and the high field of the Sales Range form.
We also have some comparison operators to define the range. So we are looking at greater than or equal to whatever's in the low field in less than or equal to whatever's in the high field. Let's go ahead and say OK to this. Let's take a look at another form. I will go ahead and open up frm_EmployeeLookup. This form has a drop-down menu that asks to show all the employees in the state of blank. Qe could select the state, run the query, and Access will show us all the employees that live in that state. Let's go ahead and build this query from scratch. I will close the one that open the qry_EmployeesLookup and we will duplicate this query.
We will create a new query in Design view. Now we are interested in our employees, so add our Employees table. Let's go ahead and say Close and we will open this us. We are interested in the FirstName, the LastName, and the State. In the State field the criteria for state is going to be whatever the value that's currently selected in our form. So we need to know the name of the specific object. Back into our form we will go into Design view. With our Property Sheet on the other tab we will select this object.
it currently has a name of state. So we are going to reference the State field on the frm_EmployeeLookup. Let's go back in to our query and then put the criteria. I will right-click in the Criteria field under the State and I will select Zoom to give us a little more room to write and we will write up a hook into the form. It looks something like this. First we need to tell Access what type of object we are looking for. We are looking for one of our forms so I will write forms. Each step needs to be separated by an exclamation mark. So I will go ahead and write Forms! The next thing we want to know is what we are looking for.
We are looking for frm_EmployeeLookup, followed by another exclamation mark, and then what field on this form are we wanting. We want the State field. So our entire code read Forms! frm_EmployeeLookup! State. Let's go ahead and say OK and we will notice that Access has cleaned up our code a little bit. It added some square brackets they are each of the elements. And if we go ahead and say View, we are currently getting no results.
That's because our form is currently in Design view. The value for this field is currently unset. Let's switch back into Datasheet view and we will select a value. I will select Hawaii. Go back into our query, switch to Design view, and then switch back to Datasheet view to rewrite it. We will notice that is now picking up the value and we are only displaying the states of Hawaii. We can go back to our form, pick a new state, how about Kentucky. In our query if we refresh it, we will get the new state showing Kentucky. Now this wasn't meant to be an all- inclusive look at creating amazing forms, but I hope these examples have given you a little taste of the types of user interface tools that are at your disposal with Access.
By combining what we already know about creating robust queries with some additional insights into the larger framework of Microsoft Access databases, my goal is to get you thinking about your own projects and hopefully get you excited about applying these tools to your own database needs.
There are currently no FAQs about Access 2010: Queries in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.