WEBVTT Kind: captions Language: en 00:00:11.970 --> 00:00:16.440 We're going to talk about a really neat type of query called a totals query. 00:00:16.440 --> 00:00:20.360 Up to this point, the queries that we've done have pulled in results from one or more tables 00:00:20.360 --> 00:00:22.930 and listed them one by one. 00:00:22.930 --> 00:00:27.140 But a totals query does more than just list the results; it combines them according to 00:00:27.140 --> 00:00:32.661 their value, and then performs a calculation such as Sum, Average, or Count so that you 00:00:32.661 --> 00:00:35.570 get much more useful information. 00:00:35.570 --> 00:00:40.640 I'm going to start by opening the Menu Items Ordered query in our bakery database. 00:00:40.640 --> 00:00:44.270 This query uses several different tables to give us a list of all of the items that have 00:00:44.270 --> 00:00:45.480 been ordered. 00:00:45.480 --> 00:00:49.320 If you look down the list you can see that there's a lot of repetition, and that's because 00:00:49.320 --> 00:00:52.220 most of these items have been ordered more than once. 00:00:52.220 --> 00:00:57.400 If we change this to a totals query, then we'll just have one row for each item, and 00:00:57.400 --> 00:01:02.600 the values in the Quantity field will be added up to show us what the total is for each item. 00:01:02.600 --> 00:01:08.040 First, you'll need to go to design view. 00:01:08.040 --> 00:01:12.140 And then click the Totals command. 00:01:12.140 --> 00:01:16.180 The design grid now has a new row called the Total row. 00:01:16.180 --> 00:01:20.970 And right now all of these say Group By, but we're going to change the one in the Quantity 00:01:20.970 --> 00:01:24.290 field by clicking the drop-down arrow. 00:01:24.290 --> 00:01:28.260 We're going to select Sum so that it will add the values together, and there are some 00:01:28.260 --> 00:01:34.820 other options such as Average, Minimum, Maximum, and Count, and a few more, but in this case 00:01:34.820 --> 00:01:35.970 we want to use Sum. 00:01:35.970 --> 00:01:38.460 none of these are going to be that useful for our purposes. 00:01:38.460 --> 00:01:42.120 Now, we can Run the query. 00:01:42.120 --> 00:01:47.460 And you can see that there is one row for each product, and the Quantity field has changed 00:01:47.460 --> 00:01:52.010 to SumOfQuantity, and this gives us the total for each item. 00:01:52.010 --> 00:01:58.740 So we can tell at a glance that we've sold 12 Christmas Cakes, 9 Carrot Cakes, and 18 00:01:58.740 --> 00:01:59.970 Cheesecakes. 00:01:59.970 --> 00:02:04.159 You may notice that some of these product names appear on more than one row, and that's 00:02:04.159 --> 00:02:06.710 because they have more than one Sales Unit. 00:02:06.710 --> 00:02:11.250 For example, the Chocolate Chip cookies can be sold by the Half-Dozen, Dozen, or as Single 00:02:11.250 --> 00:02:15.629 cookies, and these are really three different products even though they have the same product 00:02:15.629 --> 00:02:17.049 name. 00:02:17.049 --> 00:02:21.690 So now that Access is calculating these totals for us, we're able to get a much better idea 00:02:21.690 --> 00:02:46.590 of what people are buying from our bakery.