WEBVTT Kind: captions Language: en 00:00:01.839 --> 00:00:09.970 We've already talked about how to create a simple 00:00:09.970 --> 00:00:12.200 query that only uses one table. 00:00:12.200 --> 00:00:16.090 But queries that involve more than one table let you ask much more interesting questions 00:00:16.090 --> 00:00:17.090 to your database. 00:00:17.090 --> 00:00:21.570 Now this also takes a little bit more planning than a single-table query, and there are four 00:00:21.570 --> 00:00:24.490 steps that you can use to help you design your query. 00:00:24.490 --> 00:00:28.250 The first step is Pinpoint exactly what you want to find out. 00:00:28.250 --> 00:00:31.370 In other words, which question are you trying to answer? 00:00:31.370 --> 00:00:35.620 In this example, let's say our bakery is sending out coupons to our customers who live outside 00:00:35.620 --> 00:00:38.840 the city limits, to entice them to come back to our bakery. 00:00:38.840 --> 00:00:43.230 Obviously, we don't want to send them to people who live really far away - we just want to 00:00:43.230 --> 00:00:46.559 focus on people who live relatively close to Raleigh. 00:00:46.559 --> 00:00:49.890 And we're also just going to send them to customers who have previously placed orders 00:00:49.890 --> 00:00:51.410 at our bakery. 00:00:51.410 --> 00:00:55.629 The customers who meet all three of these requirements will receive coupons. 00:00:55.629 --> 00:01:00.040 So the question that we're trying to answer is 'Which customers live in our area, are 00:01:00.040 --> 00:01:04.820 outside the city limits, and have placed an order at our bakery?' 00:01:04.820 --> 00:01:08.539 The second step is to Identify the information that we need. 00:01:08.539 --> 00:01:12.159 We'll need the customers' names and their contact information. 00:01:12.159 --> 00:01:15.369 And in order to know whether they have placed an order at our bakery, we'll also need to 00:01:15.369 --> 00:01:18.329 look at the Order ID numbers. 00:01:18.329 --> 00:01:23.380 The third step is to Locate the tables that contain the information that we need. 00:01:23.380 --> 00:01:28.149 In this case, the customers' names and contact information are stored in the Customers table. 00:01:28.149 --> 00:01:31.429 And the Order ID numbers are stored in the Orders table. 00:01:31.429 --> 00:01:37.009 So that means we need both of these tables in our query. 00:01:37.009 --> 00:01:41.200 At this point, we have enough information to start creating our query. 00:01:41.200 --> 00:01:48.340 Go to the Create tab, and select Query design. 00:01:48.340 --> 00:01:55.600 We're going to add the Customers table and the Orders table. 00:01:55.600 --> 00:01:57.479 And then close this window. 00:01:57.479 --> 00:02:05.590 And you resize these windows if you need to. 00:02:05.590 --> 00:02:08.340 Then we're going to double click the fields that we need. 00:02:08.340 --> 00:02:19.870 I'm adding the customer's first name, last name, street address, city, state, zip code, 00:02:19.870 --> 00:02:22.010 and phone number. 00:02:22.010 --> 00:02:26.489 And from the Orders table, we'll need the ID field. 00:02:26.489 --> 00:02:30.440 When you have more than one table in a query, they will be connected by a line, and this 00:02:30.440 --> 00:02:32.209 is called a Join. 00:02:32.209 --> 00:02:36.200 The join will often have an arrow that points to the left or the right, which tells the 00:02:36.200 --> 00:02:39.310 query which table to look at first. 00:02:39.310 --> 00:02:43.459 Sometimes you'll need to change the direction to get the results that you want. 00:02:43.459 --> 00:02:48.140 We're going to double-click on the join to change it, and we want to select the third 00:02:48.140 --> 00:02:52.579 option which says 'Include ALL records from the Orders Table'. 00:02:52.579 --> 00:02:55.480 And in the next video, we're going to talk a little more about *why* we're choosing this 00:02:55.480 --> 00:03:00.269 option, but basically this means that it will pull from the Orders table first, which ensures 00:03:00.269 --> 00:03:05.349 that only the customers who have placed an order will be included. 00:03:05.349 --> 00:03:11.040 When you click OK, you can see that the arrow now points to the left. 00:03:11.040 --> 00:03:15.489 The fourth step is to determine which search criteria you need to use. 00:03:15.489 --> 00:03:19.790 We're going to be adding criteria under the City and Phone Number fields. 00:03:19.790 --> 00:03:24.650 First, we want to exclude all of the customers who are in Raleigh. 00:03:24.650 --> 00:03:28.680 To do this, we're going to need to use a very specific syntax. 00:03:28.680 --> 00:03:35.010 So for the City criteria, type Not In, and then in parentheses type Raleigh in quotation 00:03:35.010 --> 00:03:36.140 marks. 00:03:36.140 --> 00:03:41.450 You can use this syntax whenever you want to exclude something from the query results. 00:03:41.450 --> 00:03:45.950 Now we also need some way of limiting the results to just the nearby towns. 00:03:45.950 --> 00:03:49.489 And in this case we're going to do this by getting the area code from the Phone Number 00:03:49.489 --> 00:03:50.489 field. 00:03:50.489 --> 00:03:55.870 The 919 area code covers Raleigh and a number of nearby cities and towns, so this should 00:03:55.870 --> 00:03:58.129 give us a pretty good range. 00:03:58.129 --> 00:04:01.431 We'll need to use a syntax that looks at the beginning of each phone number. 00:04:01.431 --> 00:04:09.129 In the Phone Number column, type Like, and in parentheses, type *quote* 919, asterisk, 00:04:09.129 --> 00:04:11.340 *end quote*. 00:04:11.340 --> 00:04:15.450 The asterisk means that any phone number can come after the 919 area code. 00:04:15.450 --> 00:04:19.650 There are many other syntaxes that you can use, and in the next video we'll look at a 00:04:19.650 --> 00:04:22.430 few other examples of these. 00:04:22.430 --> 00:04:26.530 In this case we are putting the criteria on the same row, because we want the customers 00:04:26.530 --> 00:04:28.830 to meet both of these criteria. 00:04:28.830 --> 00:04:32.400 If they just meet one of the criteria, then they're not going to be included in the query 00:04:32.400 --> 00:04:33.560 results. 00:04:33.560 --> 00:04:37.400 If we needed them to meet one or the other, then we would put one of the criteria on the 00:04:37.400 --> 00:04:40.080 next row. 00:04:40.080 --> 00:04:44.020 This query is finished now, so we can Run it to see the results. 00:04:44.020 --> 00:04:48.370 And you can see that each customer meets both criteria. 00:04:48.370 --> 00:04:51.760 They are not from Raleigh, and their phone numbers begin with 919. [put boxes around 00:04:51.760 --> 00:04:53.460 city and area code] 00:04:53.460 --> 00:04:56.930 So generally, more complex queries require more planning. 00:04:56.930 --> 00:05:01.800 But you can make it a lot easier by just following the four steps of Pinpointing exactly what 00:05:01.800 --> 00:05:07.460 you want to find out, Identifying the information that you need, Locating the tables that contain 00:05:07.460 --> 00:05:12.340 the information, and Determining exactly what criteria you need. 00:05:12.340 --> 00:05:16.150 And in the next video, we're going to look at joins and search criteria in a little bit 00:05:16.150 --> 00:05:16.910 more detail.