WEBVTT Kind: captions Language: en 00:00:11.519 --> 00:00:16.820 One of the most powerful ways of analyzing your data in Access is by creating a query. 00:00:16.820 --> 00:00:20.410 Running a query is like asking your database a question. 00:00:20.410 --> 00:00:25.869 A query can retrieve data from a single table, or from multiple tables, and it all depends 00:00:25.869 --> 00:00:28.900 on how complex your question is. 00:00:28.900 --> 00:00:33.620 In this video, we're just going to focus on making a simple query that uses a single table, 00:00:33.620 --> 00:00:37.200 and this is sometimes called an advanced filter. 00:00:37.200 --> 00:00:41.649 Let's suppose our bakery has an upcoming event, and I want to get a list of all of our customers 00:00:41.649 --> 00:00:44.750 who live nearby so we can send them invitations. 00:00:44.750 --> 00:00:49.340 A query is going to be able to go into the Customers table and find the names and addresses 00:00:49.340 --> 00:00:51.870 of the nearby customers. 00:00:51.870 --> 00:00:58.980 To create a query, go to the Create tab… and then click the Query Design command. 00:00:58.980 --> 00:01:02.250 And then you'll need to select the table or tables that you want to retrieve the data 00:01:02.250 --> 00:01:03.460 from. 00:01:03.460 --> 00:01:07.680 We're just using the Customers table for now. 00:01:07.680 --> 00:01:13.969 Click Add… and then you can close this window. 00:01:13.969 --> 00:01:18.320 When working with queries, there are a couple of different views that we're going to use. 00:01:18.320 --> 00:01:24.140 If you click on the View drop-down arrow… you can see that we are in Design View. 00:01:24.140 --> 00:01:28.399 When we finish the query, we're going to be viewing the results in Datasheet View. 00:01:28.399 --> 00:01:34.139 And you can use this menu to switch views whenever you want. 00:01:34.139 --> 00:01:38.719 The Customers table appears as a small window in the Object Relationship Pane, which is 00:01:38.719 --> 00:01:40.429 this area here. 00:01:40.429 --> 00:01:44.740 And it has a list of all of the fields that are in this table, such as First Name, Last 00:01:44.740 --> 00:01:46.880 Name, and Street Address. 00:01:46.880 --> 00:01:51.670 I'm going to resize it so I can see all of the fields. 00:01:51.670 --> 00:01:56.399 What we're going to do is double-click each field that we want to include in the query. 00:01:56.399 --> 00:02:03.200 We need the customer's name and address, so we'll double click on First Name, Last Name, 00:02:03.200 --> 00:02:10.149 Street Address, City, State, and Zip Code. 00:02:10.149 --> 00:02:14.810 And we're not emailing or calling them, so we don't need those fields. 00:02:14.810 --> 00:02:20.150 Each field now appears in the area below, which is called the Design Grid. 00:02:20.150 --> 00:02:25.450 Below the field names is the Table row, which shows which table each field comes from, and 00:02:25.450 --> 00:02:28.760 this will become more important when we start dealing with multiple tables. 00:02:28.760 --> 00:02:36.140 And we're going to modify some of these blank cells to refine our query. 00:02:36.140 --> 00:02:41.140 We want the results to be sorted by last name, so in the sort row, we'll click the cell under 00:02:41.140 --> 00:02:45.600 Last Name… and a drop-down arrow will appear. 00:02:45.600 --> 00:02:48.940 You can click it and then choose how you want it to be sorted. 00:02:48.940 --> 00:02:54.260 Next, we're going to use the Criteria row to filter the records so that it's only showing 00:02:54.260 --> 00:02:56.730 the customers who live nearby. 00:02:56.730 --> 00:03:00.840 First of all, we want to send invitations to everyone who lives in Raleigh, so we'll 00:03:00.840 --> 00:03:03.310 type Raleigh in the City column. 00:03:03.310 --> 00:03:08.080 And since we're looking for an exact match, it will need to be in quotation marks. 00:03:08.080 --> 00:03:12.550 So far, this query will show all of the customers who live in Raleigh, but none of the customers 00:03:12.550 --> 00:03:14.150 in other cities. 00:03:14.150 --> 00:03:18.730 But one of the zip codes in Cary is close to Raleigh, and let's say we'd like to send 00:03:18.730 --> 00:03:21.540 those customers an invitation as well. 00:03:21.540 --> 00:03:26.460 To do this, we're going to need to add another criteria under zip code. 00:03:26.460 --> 00:03:30.890 Instead of using the Criteria row, we're going to use the row below it, which is the 'or' 00:03:30.890 --> 00:03:33.560 row. 00:03:33.560 --> 00:03:37.450 We'll type 27513 in quotation marks. 00:03:37.450 --> 00:03:44.080 So now the query will show customers who are in Raleigh or in zip code 27513. 00:03:44.080 --> 00:03:48.140 The reason why we didn't put them both on the same line, is that the customers would 00:03:48.140 --> 00:03:50.620 then need to meet both criteria. 00:03:50.620 --> 00:03:55.940 In other words, the customer would need to live in Raleigh and in zip code 27513. 00:03:55.940 --> 00:04:03.800 In some cases, you may want that, but in this example, it will not give us the correct results. 00:04:03.800 --> 00:04:09.040 Now we're finished designing this query, and the final step is to run it. 00:04:09.040 --> 00:04:14.680 Click the Run command in the Design tab… and the results will instantly appear in Datasheet 00:04:14.680 --> 00:04:18.769 View, which looks exactly like a table. 00:04:18.769 --> 00:04:22.970 If you scroll through the results, you can see that each customer lives either in Raleigh 00:04:22.970 --> 00:04:27.490 or in zip code 27513. 00:04:27.490 --> 00:04:32.779 If you want to make any changes to the query, you can click the View drop-down arrow… 00:04:32.779 --> 00:04:36.389 and go back to Design View. 00:04:36.389 --> 00:04:40.460 And just like any object, it's a good idea to save it. 00:04:40.460 --> 00:04:48.370 I'll call it Nearby Customers. 00:04:48.370 --> 00:04:52.400 This was just a basic example, and in the next video we're going to talk about how to 00:04:52.400 --> 00:04:54.979 make a query with multiple tables. 00:04:54.979 --> 00:04:59.190 You may want to practice this one a few times before you go on, to make sure you're comfortable 00:04:59.190 --> 00:05:00.180 with the process.