WEBVTT Kind: captions Language: en 00:00:11.980 --> 00:00:14.280 In the last video, we created a query that used 00:00:14.280 --> 00:00:16.730 two tables, and we just wanted our query to 00:00:16.730 --> 00:00:19.060 include customers who have placed an order at our 00:00:19.060 --> 00:00:20.060 bakery. 00:00:20.060 --> 00:00:22.320 Now, we're going to talk about why we chose the 00:00:22.320 --> 00:00:23.860 type of join that we did, 00:00:23.860 --> 00:00:26.529 and we're also going to look at some examples of search criteria 00:00:26.529 --> 00:00:29.400 that you can use to narrow down your queries. 00:00:29.400 --> 00:00:31.740 In this example, we chose a left join because we 00:00:31.740 --> 00:00:34.010 wanted the query to pull in the records from the 00:00:34.010 --> 00:00:37.239 orders table first, and then use the information in those records 00:00:37.239 --> 00:00:40.180 to retrieve the records from the Customers table. 00:00:40.180 --> 00:00:42.079 To understand how this works, let's look at how 00:00:42.079 --> 00:00:43.360 these tables are connected. 00:00:43.360 --> 00:00:46.329 Every time an order is placed, it's connected with 00:00:46.329 --> 00:00:47.480 a customer. 00:00:47.480 --> 00:00:51.900 And if someone places multiple orders, then they'll be connected with each of those orders. 00:00:51.900 --> 00:00:53.760 You'll notice that not all of our customers have 00:00:53.760 --> 00:00:54.890 placed an order, 00:00:54.890 --> 00:00:58.090 but all of the orders are connected with a customer. 00:00:58.090 --> 00:00:59.820 And that's an important thing to keep in mind when 00:00:59.820 --> 00:01:02.910 we're choosing which type of join to use. 00:01:02.910 --> 00:01:06.530 Let's look at what would happen if we connected these tables with a right join. 00:01:06.530 --> 00:01:09.229 First, Access retrieves all of the records from 00:01:09.229 --> 00:01:10.759 the Customers table, 00:01:10.759 --> 00:01:15.230 and then it uses this list to get all of the orders that are connected with a customer. 00:01:15.230 --> 00:01:17.349 That means the query is going to include all of 00:01:17.349 --> 00:01:18.560 the customer records, 00:01:18.560 --> 00:01:20.560 even if they're not connected with an order. 00:01:20.560 --> 00:01:22.200 And this is not what we want. 00:01:22.200 --> 00:01:24.979 Instead, we want to use the order records to just 00:01:24.979 --> 00:01:27.539 pull in the customers who have placed an order. 00:01:27.539 --> 00:01:29.910 We can do this by choosing a left join. 00:01:29.910 --> 00:01:32.539 Now, the query will first retrieve all of the 00:01:32.539 --> 00:01:34.090 records from the Orders table, 00:01:34.090 --> 00:01:37.119 and then it will use that list to find all of the 00:01:37.119 --> 00:01:38.119 customers who are 00:01:38.119 --> 00:01:41.760 connected with at least one order. 00:01:41.760 --> 00:01:44.229 So whenever you're creating a query with multiple tables, 00:01:44.229 --> 00:01:47.130 you'll need to decide which type of join to use. 00:01:47.130 --> 00:01:49.399 You can double-click the join to change it, 00:01:49.399 --> 00:01:56.959 and then choose Option 2 for a right join, or Option 3 for a left join. 00:01:56.959 --> 00:02:00.000 We are also narrowing down our query by using search criteria 00:02:00.000 --> 00:02:02.649 in the City and Phone Number fields. 00:02:02.649 --> 00:02:04.450 You may remember that search criteria have to be 00:02:04.450 --> 00:02:06.470 written with a very specific syntax 00:02:06.470 --> 00:02:08.500 so that Access can understand them, 00:02:08.500 --> 00:02:13.280 and they'll often need to include quotation marks and parentheses in order to be correct. 00:02:13.280 --> 00:02:16.880 So let's look at a few of the different syntaxes that you can use. 00:02:16.880 --> 00:02:19.210 If you're looking for an exact match, then you can 00:02:19.210 --> 00:02:23.079 just put your search terms in quotation marks. 00:02:23.079 --> 00:02:25.689 If you want to exclude something from the results, 00:02:25.689 --> 00:02:27.670 then you can use Not In. 00:02:27.670 --> 00:02:29.670 And you can even exclude several different things 00:02:29.670 --> 00:02:33.430 by separating them with commas. 00:02:33.430 --> 00:02:35.250 If you're looking for terms at the beginning or 00:02:35.250 --> 00:02:36.549 the end of a field, 00:02:36.549 --> 00:02:38.260 you'll need to use the Like syntax. 00:02:38.260 --> 00:02:40.760 And you'll notice that each one of these has an 00:02:40.760 --> 00:02:42.390 asterisk in it. 00:02:42.390 --> 00:02:45.159 This is known as a wildcard character, which just 00:02:45.159 --> 00:02:47.700 means that anything can go here. 00:02:47.700 --> 00:02:51.470 For example, if you're looking for phone numbers that begin with 919, 00:02:51.470 --> 00:02:54.469 then you'll type 919 asterisk. 00:02:54.469 --> 00:02:56.819 And that means that this query will look for 919 00:02:56.819 --> 00:02:59.819 followed by anything. 00:02:59.819 --> 00:03:01.920 And finally, when you're working with numbers, 00:03:01.920 --> 00:03:04.230 you can use symbols such as greater than and less 00:03:04.230 --> 00:03:05.940 than to test the values. 00:03:05.940 --> 00:03:07.709 And you can also look for numbers that are between 00:03:07.709 --> 00:03:09.390 two values. 00:03:09.390 --> 00:03:11.420 So those are some of the most common syntaxes that 00:03:11.420 --> 00:03:12.590 you can use. 00:03:12.590 --> 00:03:16.670 And you might not use all of these, but depending on what type of information you have in your 00:03:16.670 --> 00:03:19.010 database, you can probably find at least two or 00:03:19.010 --> 00:03:43.389 three that will be useful to you.