WEBVTT Kind: captions Language: en 00:00:11.800 --> 00:00:17.060 One of the most powerful ways of analyzing your data in Access is by creating a Query. 00:00:17.060 --> 00:00:20.100 Running a query is like asking your database a question. 00:00:20.100 --> 00:00:25.340 A query can retrieve data from a single table, or from multiple tables, and it all depends 00:00:25.340 --> 00:00:27.750 on how complex your question is. 00:00:27.750 --> 00:00:32.490 In this video, we're just going to focus on making a simple query that uses a single table, 00:00:32.490 --> 00:00:35.660 and this is sometimes just called an advanced filter. 00:00:35.660 --> 00:00:40.110 Let's suppose our bakery has an upcoming event, and I want to get a list of all of our customers 00:00:40.110 --> 00:00:43.239 who live nearby so we can send them invitations. 00:00:43.239 --> 00:00:47.809 A query is going to be able to go into the Customers table and find the names and addresses 00:00:47.809 --> 00:00:50.100 of the nearby customers. 00:00:50.100 --> 00:00:56.830 To create a query, go to the Create tab, and then click the Query Design command. 00:00:56.830 --> 00:01:00.100 And then you'll need to select the table or tables that you want to retrieve the data 00:01:00.100 --> 00:01:01.320 from. 00:01:01.320 --> 00:01:04.500 We're just using the Customers table for now. 00:01:04.500 --> 00:01:08.820 Click Add, and then you can close this window. 00:01:08.820 --> 00:01:12.770 When working with queries, there are a couple of different views that we're going to use. 00:01:12.770 --> 00:01:18.080 If you click on the View drop-down arrow, you can see that we are in Design view. 00:01:18.080 --> 00:01:22.360 When we finish the query, we're going to be viewing the results in Datasheet View. 00:01:22.360 --> 00:01:26.650 And you can use this menu to switch views whenever you want. 00:01:26.650 --> 00:01:31.250 The Customers table appears as a small window in the Object Relationship Pane, which is 00:01:31.250 --> 00:01:32.940 this area here. 00:01:32.940 --> 00:01:37.260 And it has a list of all of the fields that are in this table, such as First Name, Last 00:01:37.260 --> 00:01:39.210 Name, and Street Address. 00:01:39.210 --> 00:01:42.960 I'm going to resize it so I can see all of the fields. 00:01:42.960 --> 00:01:47.710 What we're going to do is double-click each field that we want to include in the query. 00:01:47.710 --> 00:01:53.860 We need the customer's name and address, so we'll double click on First Name, Last Name, 00:01:53.860 --> 00:01:59.700 Street Address, City, State, and Zip Code. 00:01:59.700 --> 00:02:04.060 And we're not emailing or calling them, so we don't need those fields. 00:02:04.060 --> 00:02:09.170 Each field now appears in the area below, which is called the Design Grid. 00:02:09.170 --> 00:02:14.470 Below the field names is the Table row, which shows which table each field comes from, and 00:02:14.470 --> 00:02:17.680 this will become more important when we start dealing with multiple tables. 00:02:17.680 --> 00:02:22.750 And we're going to modify some of these blank cells to refine our query. 00:02:22.750 --> 00:02:27.780 We want the results to be sorted by last name, so in the sort row, we'll click the cell under 00:02:27.780 --> 00:02:31.570 Last Name, and a drop-down arrow will appear. 00:02:31.570 --> 00:02:34.810 You can click it and then choose how you want it to be sorted. 00:02:34.810 --> 00:02:40.030 Next, we're going to use the Criteria row to filter the records so that it's only showing 00:02:40.030 --> 00:02:42.340 the customers who live nearby. 00:02:42.340 --> 00:02:46.430 First of all, we want to send invitations to everyone who lives in Raleigh, so we'll 00:02:46.430 --> 00:02:49.080 type Raleigh in the City column. 00:02:49.080 --> 00:02:53.540 And since we're looking for an exact match, it will need to be in quotation marks. 00:02:53.540 --> 00:02:58.030 So far, this query will show all of the customers who live in Raleigh, but none of the customers 00:02:58.030 --> 00:02:59.640 in other cities. 00:02:59.640 --> 00:03:04.220 But one of the zip codes in Cary is close to Raleigh, and let's say we'd like to send 00:03:04.220 --> 00:03:06.880 those customers an invitation as well. 00:03:06.880 --> 00:03:11.480 To do this, we're going to need to add another criteria under zip code. 00:03:11.480 --> 00:03:15.700 Instead of using the Criteria row, we're going to use the row below it, which is the 'or' 00:03:15.700 --> 00:03:17.200 row. 00:03:17.200 --> 00:03:21.100 We'll type 27513 in quotation marks. 00:03:21.100 --> 00:03:27.530 So now the query will show customers who are in Raleigh *or* in zip code 27513. 00:03:27.530 --> 00:03:32.140 The reason why we didn't put them on the same line, is that it would then find just the 00:03:32.140 --> 00:03:34.120 customers who meet *both* criteria. 00:03:34.120 --> 00:03:38.400 In other words, the customer would need to live in Raleigh *and* in zip code 27513. 00:03:38.400 --> 00:03:45.650 In some cases, you may want that, but in this example, it will not give us the correct results. 00:03:45.650 --> 00:03:50.349 Now we're finished designing this query, and the final step is to Run it. 00:03:50.349 --> 00:03:56.380 Click the Run command in the Design tab, and the results will instantly appear in Datasheet 00:03:56.380 --> 00:03:59.270 View, which looks exactly like a table. 00:03:59.270 --> 00:04:03.240 If you scroll through the results, you can see that each customer lives either in Raleigh 00:04:03.240 --> 00:04:07.470 *or* in zip code 27513. 00:04:07.470 --> 00:04:12.380 If you want to make any changes to the query, you can click the View drop-down arrow and 00:04:12.380 --> 00:04:16.100 go back to Design View. 00:04:16.100 --> 00:04:20.180 And just like any object, it's a good idea to save it. 00:04:20.180 --> 00:04:25.260 I'll call it Nearby Customers. 00:04:25.260 --> 00:04:29.300 This was just a basic example, and in the next video we're going to talk about how to 00:04:29.300 --> 00:04:31.680 make a query with multiple tables. 00:04:31.680 --> 00:04:35.910 You may want to practice this one a few times before you go on, to make sure you're comfortable 00:04:35.910 --> 00:04:59.680 with the process.