Manage tables

- [Instructor] In this video I'm going to demonstrate the options for managing tables and focus on the ones that you are most likely to see on the MOS exam. We're going to talk about how to hide fields in a table, how to add total rows so that you can summarize the information in each of the fields, add table descriptions to the table properties, and delete tables and rename tables. Notice the MOS icon to the right of several of these topics. Anytime you see the MOS icon that's an indicator to you that these will most likely show up on the MOS exam, so pay close attention to anything that has the MOS icon next to it. Let's go ahead and take a look at our 03_01 Manage Tables database. I'll go ahead and open up the 2016 Sales Table. If I want to hide a particular field in a table it's as simple as right-clicking on the field, I'll go ahead and right-click on the products field, and choose Hide Fields. To un-hide a field, it's just as easy. We just right-click on any field and go down to Un-hide Fields and it shows a list of all of the fields in this table and any of them that don't have the check mark next to them are currently hidden, so let's go ahead and put a check mark next to Product. And once again it's displayed in the table. Now what about summarizing the data directly in the table? Right on your Home tab, you have a Totals command button. When I click on that a new Total row is added to the bottom of the data, and when I click underneath any of the fields I have a dropdown menu that has all of the summary functions available to me, so for instance, underneath Item Cost, I'll use the dropdown menu and I'll select Average. And I can see that $347.12 is the average item cost for all the items in this table. For the number of items I'll go ahead and sum the total items sold and also the total cost, I'll go ahead and sum our total sales thus far in 2016. To remove the Totals row you simply click back up on the command button, and it toggles it off for you. So you can toggle it on and off at anytime. Now adding table descriptions to the properties of your table can be very helpful to users to let them know what is contained within that table. So for instance if I come over to my Navigation pane and right click on the EE Information linked table, and go down to table properties, this is where I can type a description in. I'll go ahead and type in, this table is linked to the EE data workskheet and it's updated monthly. Great information for my users to know that new information will be included in this table each month. Perfect, I'm going to go ahead and click OK. And that data has now been added to the properties of that table. What about deleting a table from your Navigation pane? Couple things to think about, if you delete a table, you can't undo it, so make absolutely certain that you want to remove the table from your database. I always recommend to my students that they make a backup of the database before they delete any tables. You also can just hide a table within the Navigation pane, so if you're not quite sure that you want to delete it, you can always hide it from view so that users don't accidentally open up the table. But if you do want to delete a table, right-click on the table name in the Navigation pane and choose Delete. A pop-up window will appear that says Do you want to delete this table? It's going to remove it from all groups, so if you have this table included in a query or report, it'll go ahead and remove it from that query or that report. And if you're sure you want to delete it, you can say yes. The table has now been removed from the database. Last but not least, let's talk about how to rename a table. It's so important to make sure that the table name truly reflects the information that's contained in the table. The EE Information table that is linked to an Excel workbook it would be great if this name reflected the information that this particular table is linked to an Excel workbook, so by right-clicking on the table name and going to Rename I can put in a name that's a little more descriptive, EE Info and a hypen and linked. Cuz some of my users might not recognize the Excel icon and know that that means it's a linked table. So by renaming it something that's a little more meaningful it'll be extremely helpful to my users. As you can see it's not difficult to maintain and manage your tables and it's very important to make sure the names of the tables and any additional information added to the table properties correctly reflect the data within the table. Using the Totals row function will give other users the ability to quickly summarize the data in a field.