From the course: Access 2019: Queries
Understand queries
- [Instructor] At the heart of every database is, of course, data, lots of data. Data tables could easily grow to hundreds, thousands, or even millions of records. But trying to manage and make sense of such large amounts of data can quickly become overwhelming. At some point you're going to want to know what does all this data tell us. Luckily Access provides a set of objects called queries to help us out. To run a query is just 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 queries is less about getting the right answer, and more about learning how to ask the right questions. Let's suppose that we're preparing an email newsletter. The first thing we would want to ask ourselves is well who are we sending them to? In the H+ Sport database we could find some information for that in the Customers Table. In this table we could find the first name, last name, as well as the email address for the customer. Now I can pull this information out of this table but there's a lot of additional information in this table that we don't need. For instance, their phone number or their address Let's go ahead and run this query down here called Customer Email Address. Well you see that it only returns the information that we need, the first name, last name, and the email. When I run the query by double clicking on it, what we get back looks and functions just like a table, but it's actually a record set. It's a temporary table that's built from the components of the underlying data tables In this case, we're simply pulling out the columns from the original customer's table. What we're seeing is a live link to the original data. If I make a change here, let's go ahead and change Amber Reyes' email address from A Reyes to Amber Reyes, and I'll press Enter to make that change. If I go back in here to the Customers Table, and take a look at the first row here for Amber Reyes, and scroll over here to the far right, we can see that that email address has been updated. If I change it here in the Data Table, let's go ahead and change it back, when I come back over to the Clear Record Set, we'll see that it's updated there as well. So changes in a query go both ways. This brings up an important concept inside of an Access Database. Queries do not actually store any data. They merely store the instructions on how to assemble the data from your tables. This way queries are always up to date without you having to maintain the same information in two or more places. So now we've got our customers and their email addresses. But now let's suppose that we only wanted to target customers in the Western region of the United States. We saw that the original Customers Table has a column here for states. For instance I can see that Amber Reyes lives in the state of Utah. If I take that piece of information and come over here to the States Table, and I'll scroll down towards the bottom, I'll find that the state of Utah belongs in the West region of the United States. So that takes of one of our thousand customers. But we don't want to actually have to look up this information for all thousand customers manually to see if they live in a Western state for the newsletter. Let's go ahead and run another query here called Customer Email by Region. This takes the original customer email and adds on two columns, the State Column, from the Customers Table, as well as the Region Column from the Related States Table. Now we're seeing the results of the two tables put together. Access behind the scenes is doing all the hard work of pairing the state abbreviation with our corresponding regions, and it displays all that information for us here at once. Now to find the customers that live in a Western region, I can go ahead and just apply a simple filter to this Region column. I'll click on the downer pointing arrow to the right-hand side, and I'll choose the checkbox that says Select All Deselect Everything, and then I'll place a checkbox here where it says West. I'll say OK and that filters down my results here to just the 199 records for the customers that live in a Western region state. Now if this is information you want to get out regularly we can cut out the step of having to filter the results by building it right into the query itself. I've got another query here called Customer Email Western Region, and I'll double click on it to open it up. And at first it actually doesn't look like anything has changed, we're still looking at a total of 199 records here. The difference here is that the region filter is built right into the query results. I didn't actually have to filter this column. So while using filters on your tables and queries can be a quick and dirty way to gain insight into some aspect of your data, queries will often work out better for you in the long run. The ability to save and rerun a query is just one of the many advantages that we'll explore throughout this title. As we'll see queries will be able to provide us answers to some pretty complex requests. Of course, we only need to learn how to ask the right questions first.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.