WEBVTT Kind: captions Language: en 00:00:01.170 --> 00:00:11.590 In 00:00:11.590 --> 00:00:16.200 the last video, we created a query that used two tables, and we just wanted our query to 00:00:16.200 --> 00:00:19.750 include customers who have placed an order at our bakery. 00:00:19.750 --> 00:00:23.700 Now we're going to talk about why we chose the type of join that we did, and we're also 00:00:23.700 --> 00:00:28.970 going to look at some examples of search criteria that you can use to narrow down your queries. 00:00:28.970 --> 00:00:33.190 In this example, we chose a right-to-left join because we wanted the query to pull in 00:00:33.190 --> 00:00:37.510 the records from the Orders table first, and then use the information in those records 00:00:37.510 --> 00:00:40.670 to retrieve the records from the Customers table. 00:00:40.670 --> 00:00:45.940 To understand how this works, let's look at how these tables are connected. 00:00:45.940 --> 00:00:49.309 Every time an order is placed, it's connected with a customer. 00:00:49.309 --> 00:00:53.959 And if someone places multiple orders, then they'll be connected with each of those orders. 00:00:53.959 --> 00:00:58.149 You'll notice that not all of the customers have placed an order, but all of the orders 00:00:58.149 --> 00:01:00.069 are connected with a customer. 00:01:00.069 --> 00:01:03.309 And that's an important thing to keep in mind when we're choosing which type of join to 00:01:03.309 --> 00:01:05.100 use. 00:01:05.100 --> 00:01:09.390 Let's look at what would happen if we connected these tables with a left-to-right join. 00:01:09.390 --> 00:01:14.280 First, Access retrieves all of the records from the Customers table, and then it uses 00:01:14.280 --> 00:01:18.340 this list to get all of the orders that are connected with a customer. 00:01:18.340 --> 00:01:22.289 That means the query is going to include all of the customer records, even if they're not 00:01:22.289 --> 00:01:23.659 connected with an order. 00:01:23.659 --> 00:01:25.640 And this is not what we want. 00:01:25.640 --> 00:01:30.189 Instead, we want to use the order records to just pull in the customers who have placed 00:01:30.189 --> 00:01:31.280 an order. 00:01:31.280 --> 00:01:34.190 We can do this by choosing a right-to-left join. 00:01:34.190 --> 00:01:39.031 Now, the query will first retrieve all of the records from the Orders table, and it 00:01:39.031 --> 00:01:42.810 will then use that list to find all of the customers who are connected with at least 00:01:42.810 --> 00:01:47.040 one order. 00:01:47.040 --> 00:01:51.080 So whenever you're creating a query with multiple tables, you'll need to decide which type of 00:01:51.080 --> 00:01:52.789 join to use. 00:01:52.789 --> 00:01:59.310 You can double-click the join to change it… then choose Option 2 for a right join, or 00:01:59.310 --> 00:02:05.950 Option 3 for a left join. 00:02:05.950 --> 00:02:10.440 We're also narrowing down our query by using search criteria in the City and Phone Number 00:02:10.440 --> 00:02:11.640 fields. 00:02:11.640 --> 00:02:15.459 You may remember that search criteria have to be written with a very specific syntax 00:02:15.459 --> 00:02:19.860 so that Access can understand them, and they'll often need to include quotation marks and 00:02:19.860 --> 00:02:22.080 parentheses in order to be correct. 00:02:22.080 --> 00:02:27.730 So let's look at a few of the different syntaxes that you can use. 00:02:27.730 --> 00:02:31.879 If you're looking for an exact match, then you can just put your search terms in quotation 00:02:31.879 --> 00:02:33.920 marks. 00:02:33.920 --> 00:02:38.510 If you want to exclude something from the results, you can use Not In. 00:02:38.510 --> 00:02:44.840 And you can even exclude several different things by separating them with commas. 00:02:44.840 --> 00:02:48.500 If you're looking for terms at the beginning or the end of a field, you'll need to use 00:02:48.500 --> 00:02:49.659 the Like syntax. 00:02:49.659 --> 00:02:53.989 And you'll notice that each one of these has an asterisk in it. 00:02:53.989 --> 00:02:59.280 This is known as a wildcard character, which just means that anything can go here. 00:02:59.280 --> 00:03:06.030 For example, if you're looking for phone numbers that begin with 919, then you'll type "919*". 00:03:06.030 --> 00:03:12.430 And that means this query will look for 919 followed by anything. 00:03:12.430 --> 00:03:16.610 And finally, when you're working with numbers, you can use symbols such as greater than and 00:03:16.610 --> 00:03:18.740 less than to test the values. 00:03:18.740 --> 00:03:22.790 And you can also look for numbers that are between two values. 00:03:22.790 --> 00:03:25.980 So those are some of the most common syntaxes that you can use. 00:03:25.980 --> 00:03:30.060 And you might not use all of these, but depending on what type of information you have in your 00:03:30.060 --> 00:03:33.999 database, you can probably find at least two or three that will be useful to you.