WEBVTT Kind: captions Language: en 00:00:06.810 --> 00:00:12.000 You have already seen how an advanced filter is really a very basic query, run on only 00:00:12.000 --> 00:00:13.820 one table in your database. 00:00:13.820 --> 00:00:18.699 Well, sometimes, you are going to want to be able to pull out information from more 00:00:18.699 --> 00:00:24.990 than one table at a time and combine it in a meaningful way so that you can analyze it. 00:00:24.990 --> 00:00:29.949 That is what a query can do for you - it pulls out the data you tell it to, from the tables 00:00:29.949 --> 00:00:35.620 you tell it to look in, and then shows it to you in a way that is easy for you to analyze. 00:00:35.620 --> 00:00:40.470 But, you have to be very careful in the way you tell it to get your information, or it 00:00:40.470 --> 00:00:44.340 may not return the results you expected. 00:00:44.340 --> 00:00:50.250 Let's talk about how to design a query before we even get into using Access to set it up. 00:00:50.250 --> 00:00:55.290 Queries help you answer questions you may have about your data, like "Which customers 00:00:55.290 --> 00:00:58.370 have ordered technology books"? 00:00:58.370 --> 00:01:04.040 Before we go much further, I think it will help if we talk about some query design basics. 00:01:04.040 --> 00:01:09.740 You don't need to know how to write in a formal query language to use Access, but understanding 00:01:09.740 --> 00:01:16.170 how queries work and using a process to design your own query can certainly make the process 00:01:16.170 --> 00:01:21.280 easier and increases the likelihood that you'll get the results you expect. 00:01:21.280 --> 00:01:26.020 To make any query work, you need to know three things. 00:01:26.020 --> 00:01:28.979 What FIELDS do you want to see in the results? 00:01:28.979 --> 00:01:34.869 In this step, you are selecting the fields you want Access to show you in your results. 00:01:34.869 --> 00:01:39.329 Which TABLES or queries hold the info you need? 00:01:39.329 --> 00:01:44.759 In this step, you're telling the Access where to get the information from. 00:01:44.759 --> 00:01:47.389 What CONDITIONS do you want the data to meet? 00:01:47.389 --> 00:01:51.909 This is telling Access that you want to see all the records where a certain condition 00:01:51.909 --> 00:01:54.909 is met. 00:01:54.909 --> 00:01:59.140 Let's plan this out on paper for our first question. 00:01:59.140 --> 00:02:02.009 Which customers have ordered technology books? 00:02:02.009 --> 00:02:06.469 We have a new technology series, and we want to send these customers a coupon for the next 00:02:06.469 --> 00:02:07.570 book release. 00:02:07.570 --> 00:02:09.819 So let's start planning. 00:02:09.819 --> 00:02:14.400 First, we need to select the FIELDS we want to see in our results. 00:02:14.400 --> 00:02:19.319 If we want to send coupons to our customers, we'd obviously want the customer's name and 00:02:19.319 --> 00:02:27.360 address fields so the fields we will select are: First Name, Last Name, Street, City, 00:02:27.360 --> 00:02:29.790 State, and Zip. 00:02:29.790 --> 00:02:31.920 These are in the Customers table. 00:02:31.920 --> 00:02:37.260 Now, which TABLES hold these fields that we need? 00:02:37.260 --> 00:02:41.930 We already know that we need the Customers table for the names and addresses but we're 00:02:41.930 --> 00:02:48.500 also going to need the Books table to tell us which books are technology books. 00:02:48.500 --> 00:02:53.430 And we'll need the Orders table, because that is where a book is joined with a customer. 00:02:53.430 --> 00:02:57.159 Now...What CONDITIONS do we want the data to meet? 00:02:57.159 --> 00:03:02.620 Well, we want to see ONLY the customer names and addresses for customers who have ordered 00:03:02.620 --> 00:03:08.019 books that have a category of Technology. 00:03:08.019 --> 00:03:12.519 Now that we have a plan, we can start building a query in Access. 00:03:12.519 --> 00:03:14.550 We are going to build this query from scratch. 00:03:14.550 --> 00:03:20.810 You do that with the Query Design command on the Create tab on the Ribbon. 00:03:20.810 --> 00:03:27.590 When the Show Table dialog box opens, pick all the tables that you outlined on your plan. 00:03:27.590 --> 00:03:33.189 We need the Customers table, the Orders table and the Books table, so click on them to add 00:03:33.189 --> 00:03:36.379 them. 00:03:36.379 --> 00:03:40.680 Now I need to look at my plan and decide what fields I need from each table. 00:03:40.680 --> 00:03:46.280 Well, I want to see the Customer's first and last name, and their street address, city, 00:03:46.280 --> 00:03:47.439 state, and zip. 00:03:47.439 --> 00:03:52.209 So I'll highlight those fields and drag them down to the bottom portion of the screen. 00:03:52.209 --> 00:03:59.799 I also need the Category field from the Books table, so I'll drag that down there, too. 00:03:59.799 --> 00:04:03.812 These are all the fields I care about so now I have to tell Access how to show them to 00:04:03.812 --> 00:04:04.900 me. 00:04:04.900 --> 00:04:15.080 I want only books with a category of Technology, so in Criteria, I'll type Technology. 00:04:15.080 --> 00:04:19.519 Access puts the quotes around it, because that is the EXACT word it is looking for in 00:04:19.519 --> 00:04:21.590 the books table. 00:04:21.590 --> 00:04:27.990 Now all you have to do is hit Run! in the Results tab to run the query. 00:04:27.990 --> 00:04:32.240 If your results don't look like you expect them to, you may not have included everything 00:04:32.240 --> 00:04:34.620 from your plan. 00:04:34.620 --> 00:04:36.180 But ours look good. 00:04:36.180 --> 00:04:40.530 By default whatever you drag to the bottom of your screen shows up in your results. 00:04:40.530 --> 00:04:45.550 We really don't need to see the category so I'm going to show you how to remove this. 00:04:45.550 --> 00:04:56.460 Go back to Design view and we're going to uncheck the box on the show row above technology. 00:04:56.460 --> 00:04:59.220 That way the category won't show in our results. 00:04:59.220 --> 00:05:04.450 Don't forget to save your query - we may want to run this query again the next time we have 00:05:04.450 --> 00:05:07.090 a technology book released. 00:05:07.090 --> 00:05:11.870 Save it as Customers of Tech Books or something else logical. 00:05:11.870 --> 00:05:18.210 Okay, so now you've seen how to plan your query out on paper first and how to use the 00:05:18.210 --> 00:05:21.699 Query Design tool to set up a simple select query. 00:05:21.699 --> 00:05:26.419 Next, we'll look at how to use the Query Wizard to find out which book is our bestseller.