WEBVTT Kind: captions Language: en 00:00:08.090 --> 00:00:13.210 One of the most powerful ways of analyzing your data in Access is by creating a Query. 00:00:13.210 --> 00:00:16.680 Running a query is like asking your database a question. 00:00:16.680 --> 00:00:21.900 A query can retrieve data from a single table, or from multiple tables, and it all depends 00:00:21.900 --> 00:00:24.930 on how complex your question is. 00:00:24.930 --> 00:00:29.460 In this video, we're just going to focus on making a simple query that uses a single table, 00:00:29.460 --> 00:00:33.230 and this is sometimes just called an advanced filter. 00:00:33.230 --> 00:00:37.690 Let's suppose our bakery has an upcoming event, and I want to get a list of all of our customers 00:00:37.690 --> 00:00:40.780 who live nearby so we can send them invitations. 00:00:40.780 --> 00:00:45.370 A query is going to be able to go into the Customers table and find the names and addresses 00:00:45.370 --> 00:00:47.890 of the nearby customers. 00:00:47.890 --> 00:00:58.440 To create a query, go to the Create tab, and then click the Query Design command. 00:00:58.440 --> 00:01:01.710 And then you'll need to select the table or tables that you want to retrieve the data 00:01:01.710 --> 00:01:02.930 from. 00:01:02.930 --> 00:01:07.130 We're just using the Customers table for now. 00:01:07.130 --> 00:01:12.759 Click Add, and then you can close this window. 00:01:12.759 --> 00:01:16.750 When working with queries, there are a couple of different views that we're going to use. 00:01:16.750 --> 00:01:22.759 If you click on the View drop-down arrow, you can see that we are in Design view. 00:01:22.759 --> 00:01:27.030 When we finish the query, we're going to be viewing the results in Datasheet View. 00:01:27.030 --> 00:01:31.130 And you can use this menu to switch views whenever you want. 00:01:31.130 --> 00:01:37.349 The Customers table appears as a small window in the Object Relationship Pane, which is 00:01:37.349 --> 00:01:39.070 this area here. 00:01:39.070 --> 00:01:43.369 And it has a list of all of the fields that are in this table, such as First Name, Last 00:01:43.369 --> 00:01:45.509 Name, and Street Address. 00:01:45.509 --> 00:01:50.600 I'm going to resize it so I can see all of the fields. 00:01:50.600 --> 00:01:55.320 What we're going to do is double-click each field that we want to include in the query. 00:01:55.320 --> 00:02:02.130 We need the customer's name and address, so we'll double click on First Name, Last Name, 00:02:02.130 --> 00:02:09.090 Street Address, City, State, and Zip Code. 00:02:09.090 --> 00:02:13.750 And we're not emailing or calling them, so we don't need those fields. 00:02:13.750 --> 00:02:19.090 Each field now appears in the area below, which is called the Design Grid. 00:02:19.090 --> 00:02:24.379 Below the field names is the Table row, which shows which table each field comes from, and 00:02:24.379 --> 00:02:27.689 this will become more important when we start dealing with multiple tables. 00:02:27.689 --> 00:02:32.879 And we're going to modify some of these blank cells to refine our query. 00:02:32.879 --> 00:02:37.879 We want the results to be sorted by last name, so in the sort row, we'll click the cell under 00:02:37.879 --> 00:02:42.340 Last Name, and a drop-down arrow will appear. 00:02:42.340 --> 00:02:45.670 You can click it and then choose how you want it to be sorted. 00:02:45.670 --> 00:02:51.000 Next, we're going to use the Criteria row to filter the records so that it's only showing 00:02:51.000 --> 00:02:53.310 the customers who live nearby. 00:02:53.310 --> 00:02:57.400 First of all, we want to send invitations to everyone who lives in Raleigh, so we'll 00:02:57.400 --> 00:03:00.040 type Raleigh in the City column. 00:03:00.040 --> 00:03:04.810 And since we're looking for an exact match, it will need to be in quotation marks. 00:03:04.810 --> 00:03:09.280 So far, this query will show all of the customers who live in Raleigh, but none of the customers 00:03:09.280 --> 00:03:10.890 in other cities. 00:03:10.890 --> 00:03:15.459 But one of the zip codes in Cary is close to Raleigh, and let's say we'd like to send 00:03:15.459 --> 00:03:18.269 those customers an invitation as well. 00:03:18.269 --> 00:03:23.200 To do this, we're going to need to add another criteria under zip code. 00:03:23.200 --> 00:03:27.620 Instead of using the Criteria row, we're going to use the row below it, which is the 'or' 00:03:27.620 --> 00:03:30.290 row. 00:03:30.290 --> 00:03:34.180 We'll type 27513 in quotation marks. 00:03:34.180 --> 00:03:40.810 So now the query will show customers who are in Raleigh *or* in zip code 27513. 00:03:40.810 --> 00:03:45.409 The reason why we didn't put them on the same line, is that it would then find just the 00:03:45.409 --> 00:03:47.349 customers who meet *both* criteria. 00:03:47.349 --> 00:03:51.730 In other words, the customer would need to live in Raleigh *and* in zip code 27513. 00:03:51.730 --> 00:04:00.540 In some cases, you may want that, but in this example, it will not give us the correct results. 00:04:00.540 --> 00:04:05.439 Now we're finished designing this query, and the final step is to Run it. 00:04:05.439 --> 00:04:11.249 Click the Run command in the Design tab, and the results will instantly appear in Datasheet 00:04:11.249 --> 00:04:15.180 View, which looks exactly like a table. 00:04:15.180 --> 00:04:19.120 If you scroll through the results, you can see that each customer lives either in Raleigh 00:04:19.120 --> 00:04:24.250 *or* in zip code 27513. 00:04:24.250 --> 00:04:29.550 If you want to make any changes to the query, you can click the View drop-down arrow and 00:04:29.550 --> 00:04:33.150 go back to Design View. 00:04:33.150 --> 00:04:37.220 And just like any object, it's a good idea to save it. 00:04:37.220 --> 00:04:43.130 I'll call it Nearby Customers. 00:04:43.130 --> 00:04:47.160 This was just a basic example, and in the next video we're going to talk about how to 00:04:47.160 --> 00:04:49.750 make a query with multiple tables. 00:04:49.750 --> 00:04:53.950 You may want to practice this one a few times before you go on, to make sure you're comfortable 00:04:53.950 --> 00:04:54.950 with the process.