Join Adam Wilbert for an in-depth discussion in this video Editing table structure in Design view, part of Office 365: Access Essential Training.
- All of the objects that we're going to be creating in Access have multiple ways to work with them. These are called Views, and you can switch between the available Views by using the very first button here on the Home tab, which is this View button here. Let's go ahead and double-click on the Guests table to open it up to activate that button here. And you'll notice that it's a split button. I have a toggle here on the top that'll toggle between the two default states. And then if I click on the bottom half of the button here, you'll see some additional options, if there are any for the particular item that you're working with here. In this case, tables only have two Views. We have Datasheet View, and we have Design View.
Currently, we're in Datasheet View, so that's why you see the upper half of the button will switch you into Design View. And if you click on that, it'll take you into something called Design View, and we'll talk about this in a moment. And you'll notice that that button changes to indicate that we'll switch back to Datasheet View by clicking it again. So let's go ahead and switch into Design View on this table here, and we'll take a look at it. In Design View, the fields for our table are listed here in this very first column. So you can see we have the field names of ID, FirstName, LastName, and so on. We can also see all of the Data Types for each of our fields.
So the ID is in AutoNumber Data Type, and then everything else is Short Text. The third column here in the Design View for our table is a Description, and you'll notice that it's optional, and currently I don't have any descriptions. But this is a good place to type in some text to document what it is that your table is trying to store. So, for instance, I'll come down here to the phone number field, and I'll type in that it's supposed to be a "9 digit phone number plus country code." So, this little bit of description text is just available to people who are viewing this table in Design View, and it might help them understand what it is that they're supposed to be storing in this particular field.
We can also modify the fields that make up our data table here in Design View. Now, in some respects, it's actually easier to create a table in Design View than it was in the Datasheet View like we did earlier. If I wanted to add in a new field called PostalCode, all I need to do is go ahead and click here into this next blank line, and I'll just type in the name of the field, which is PostalCode. I'll go ahead and press the Tab key to move over to our Data Type, and we can use the drop-down menu to select a Data Type from the list here. In this case, I'm going to choose the Short Text option here, and the description is going to be "zip or international postal code." We can now switch into Datasheet View to see the results of this change, so I'll go back up here.
And notice that I'm on the Design tab now, but I still have that View button, so it's available in two different locations: on the Table contextual tab here, this Design tab, we can find it here at the beginning, and also on the Home tab in the very same position here. Either way, go ahead and press on this button here to switch back into Datasheet View. Access is going to prompt you to save the table first, because we're making structural changes to the design of the table, so I'll go ahead and say "Yes," that I do want to save those changes. And we can see now that I have a new column here called PostalCode. Let's go ahead and switch back into Design View.
Another thing we can do in Design View is make changes to existing fields. So, for instance, this field called ID here, maybe I want to change that to read GuestID instead of just ID. So I can go ahead and highlight that text, then just type in GuestID as the new name. We can also change Data Types. So, right now this Email field is currently being stored as Short Text. If I want to make it a clickable link, though, I can change this to a Hyperlink here. So, now that'll update the Data Type for the Email. One other thing we can do is rearrange the order of our columns in our data table. So, for instance, if I want to move the PostalCode between the Phone Number and the State fields, all I need to do is click on this grey box here to the left to select that row, and then we'll click again and drag to put it into the correct position here.
Finally, I want to point out that you can quickly identify which field is the primary key when looking at your table in Design View. In this case, it's indicated by the yellow key icon up here for the GuestID, but this information would be difficult to find when you're looking at your table in Datasheet View, because there's no real indication on which column is the primary key for the table. Let's go ahead and switch back into Datasheet View one more time. And, once again, we'll save the structural changes to our table by pressing the Yes button. And you can see now that the PostalCode is in a new location here. The email addresses are clickable links now, and the new column name is GuestID for the ID field.
Now, we're going to look at the Design View in a little bit more depth in the next movie, including the bottom half of the screen, which deals with some additional field properties for each field. Because changing our View is a very common task throughout Access, there's actually a bunch of additional ways to do it. From the Navigation pane, you can simply right-click on any of your Objects and switch between the two available Views here. So, in this case, Open would be the Datasheet View, and then we have the Design View option. We saw that the option was available on the Home tab here in the first position, as well as one of the contextual tabs, here, the "Fields" tab, here in the very first position, and that's consistent with all of the other Objects as well.
And, finally, we have two additional buttons on the very bottom right-hand side of your screen to switch between the available Views. So I can switch into Datasheet View with this button, and Design View with the button in the far right-hand corner. So, we have lots of options to toggle our views between the different states that are available.
The course also shows you how to build queries and action queries, create and design forms, use macros, integrate Access with the rest of the Office 365 suite, and maintain your databases over time.
- Creating a new database
- Creating tables and new data types
- Importing and entering data
- Setting up relationships and primary keys
- Adding validation rules
- Sorting and filtering table data
- Building queries
- Designing forms
- Creating reports
- Attaching macros to buttons and tables
- Working with Excel and Outlook data
- Maintaining an Access database