WEBVTT Kind: captions Language: en 00:00:01.839 --> 00:00:10.780 We're going to talk about a really neat type of query called 00:00:10.780 --> 00:00:12.559 a totals query. 00:00:12.559 --> 00:00:16.519 Up to this point, the queries that we've done have pulled in results from one or more tables 00:00:16.519 --> 00:00:19.030 and listed them one by one. 00:00:19.030 --> 00:00:23.250 But a totals query does more than just list the results; it combines them according to 00:00:23.250 --> 00:00:28.750 their value, and then performs a calculation such as Sum, Average, or Count so that you 00:00:28.750 --> 00:00:31.710 get much more useful information. 00:00:31.710 --> 00:00:36.940 I'm going to start by opening the Menu Items Ordered query in our bakery database. 00:00:36.940 --> 00:00:40.570 This query uses several different tables to give us a list of all of the items that have 00:00:40.570 --> 00:00:42.120 been ordered. 00:00:42.120 --> 00:00:45.960 If you look down the list you can see that there's a lot of repetition, and that's because 00:00:45.960 --> 00:00:49.059 most of these items have been ordered more than once. 00:00:49.059 --> 00:00:54.240 If we change this to a totals query, then we'll just have one row for each item, and 00:00:54.240 --> 00:00:59.640 the values in the Quantity field will be added up to show us what the total is for each item. 00:00:59.640 --> 00:01:05.140 First, you'll need to go to design view. 00:01:05.140 --> 00:01:08.900 And then click the Totals command. 00:01:08.900 --> 00:01:12.930 The design grid now has a new row called the Total row. 00:01:12.930 --> 00:01:17.700 And right now all of these say Group By, but we're going to change the one in the Quantity 00:01:17.700 --> 00:01:21.650 field by clicking the drop-down arrow. 00:01:21.650 --> 00:01:25.849 We're going to select Sum so that it will add the values together, and there are some 00:01:25.849 --> 00:01:32.560 other options such as Average, Minimum, Maximum, and Count, and a few more, but in this case 00:01:32.560 --> 00:01:33.649 we want to use Sum. 00:01:33.649 --> 00:01:35.319 none of these are going to be that useful for our purposes. 00:01:35.319 --> 00:01:38.299 Now, we can Run the query. 00:01:38.299 --> 00:01:43.520 And you can see that there is one row for each product, and the Quantity field has changed 00:01:43.520 --> 00:01:48.499 to SumOfQuantity, and this gives us the total for each item. 00:01:48.499 --> 00:01:55.899 So we can tell at a glance that we've sold 12 Christmas Cakes, 9 Carrot Cakes, and 18 00:01:55.899 --> 00:01:57.380 Cheesecakes. 00:01:57.380 --> 00:02:01.389 You may notice that some of these product names appear on more than one row, and that's 00:02:01.389 --> 00:02:04.039 because they have more than one Sales Unit. 00:02:04.039 --> 00:02:08.560 For example, the Chocolate Chip cookies can be sold by the Half-Dozen, Dozen, or as Single 00:02:08.560 --> 00:02:12.939 cookies, and these are really three different products even though they have the same product 00:02:12.939 --> 00:02:14.989 name. 00:02:14.989 --> 00:02:19.620 So now that Access is calculating these totals for us, we're able to get a much better idea 00:02:19.620 --> 00:02:21.730 of what people are buying from our bakery.