From the course: Access 2016: Queries

Understand queries

From the course: Access 2016: Queries

Start my 1-month free trial

Understand queries

“

- [Voiceover] At the heart of database is of course data. Lots of data. Data tables can easily grow to hundreds, thousands, or even millions or records. But trying to manage it, it makes sense if such large amounts of data can quickly become overwhelming. At some point, you're gonna wanna know what does all of this data tell us? Luckily, Access provide a set of objects called queries to help us out. To run a query, you simply ask a question of your data. What customers live in California? How many products did we sell last month? What employees have earned a performance bonus? You ask a question and a query will return the answer. At its most basic level, learning query is less about getting the right answer, and more about learning how to ask the right questions. Let's suppose we're preparing an email newsletter. The first thing we want to ask ourselves is well, who are we sending them to? Let's go ahead and take a look at our customers' table. And we can see that we have the customers' first names and last names, as well as their email address way over here. But, we also have lots of information that we don't need. For instance, their phone number or their address details. If all we want to do is get a listing of our customers' on their email addresses, we can use a query to simplify this view. Let's go ahead and close this down, and we'll go down here and find in the query section of our navigation pane. We'll find the customer email query. Let's go ahead and double click on it to run it. Now what we get back, looks and functions just a data table. But it's actually a record set. It's a temporary table from the components of the underlying data tables. This query here is simply pulling out the first name, last name, and email fields from the customers' table. Now this record set represents a live link back to the original data that is stored in the data tables. And we can make edits here, right inside of this query result that'll translate back to the data table. We can make changes here inside the query record set, for instance if we change Amber Reyes' email address, from A dot Reyes to Amber. I'll just go ahead and type that in here and press enter. Let's go ahead and take a look at the customers' table. And we can see that change has occurred way over here in the email field. We can also make a change here in the data table. Let's go ahead and select this here just change it back to A. Go ahead highlight this and press backspace key on my keyboard. And once again I'll press enter to finalize that change. And now if I come back here to the customers' email query, you can see that data has been replaced. So this brings up an important concept inside of an Access database. Queries don't store any data, they merely store the instructions on how to assemble the data from your data tables. This way queries are always up to date without you having to maintain the same information in two or more places. Now let's say our email campaign wants to target just the customers that live in the western region of the United States. Now the customers' table only stores the state that they're from, but it doesn't say anything about the region that that state is in. We can find that information by coming back over here to the customers' table. And we can see that Amber lives in the state of Utah. Then, we'll come down to the related states table here. And, I'll scroll down until I find the state of Utah. And I can take a look at this field right over here. So I can see that Utah is indeed in the western region of the United States. Now, I have a thousand customers in this data table. And I don't want to have to go through each one to identify just the customers that are in a western region state. Instead, what we could do is use a query to help us out. Let's go ahead and run this query here: the customers' email by region, I'll double on it to run it, and you'll see the results over here. Now we're seeing two data tables that are being put together by the query. The customer name, email, and state is coming out of the customer table. And the related region information is coming out of the states table. Access behind the scene is doing all the hard work of pairing the state abbreviation with the corresponding regions into displays all that information at once for us here. Now all I need to do, in order to identify our western region customers is to come up here to the downward pointing arrow on the region column. I'll click here and I can do a simple text filter by un-selecting all here, and then clicking on the checkbox next to west. When I press OK, we'll see that I get a listing of just my customers that live in the western region. And again if I take a look down here at the very bottom, I'll see that I have 199 customers that meet that criteria. Now if this is information that we want to get at regularly, we can cut out a step of having to filter the results by building it right into the instructions for the query. In fact we have a query that does just that. Right here the customer email west region query. Let's go ahead and double click on it. And you'll see that it doesn't look like the screen updated much, but you can tell that we're in a new query by taking a look at the tab up here at the top. And this query stores the information to sort this based off of the region name, so it's already filtering it to the western customers. And we can see we get the same results, 199 customers. So while using filters on your data tables and queries can be a quick and dirty way to get insights into some aspects of your data. Queries will actually workout better for you in the long run. The ability to save and rerun a query is just one of the many advantages that we will explore throughout this title. As we'll see, queries will be able to provide answers to some pretty complex requests. Of course, we only need to learn how to ask the right questions first.

Contents