Create a table and set data types

- [Instructor] When creating a place for our data to live, we'll define the columns or the fields that'll house the data in the table. Remember that in a properly normalized data table, the fields will represent the smallest piece of information possible, so instead of a single name field, we'll instead break it into a first name, last name, or even add middle name, suffix, or prefix fields as needed. Let's create a table to hold information about the Landon Hotel's guests and I'll do this in a new blank database that we created in the last chapter. Now, there's a couple of different ways to create new tables in Access, and they all start with the Create tab in the Tables group.

The last option is to create SharePoint lists on a SharePoint server, which isn't what we're doing right now. The first option is what we're going to do. This'll create a table in data sheet view, but we also have the option to create a table in design view. Let's go ahead and click on the first button to create a table in data sheet view. When I do that, I get my new table tab here. This is in the main portion of the interface, and Access automatically adds its first column called ID. Now, this is an automatically numbering field which will give each record its primary key, but I like to be more specific about what this data is, so, rather than just leaving the name ID, I'm gonna go back and click on it, and then double-click on it to highlight it, and change the name to GuestID.

Then I'll press Enter and the next column gets highlighted. The second field is where we're going to store the first name of our guest. The first thing we need to do, though, is identify the type of data that we're going to be storing in this column. The guest's name is a text data type, so I'm gonna go ahead and choose Short Text from the list. Then, it'll highlight its name, and I'll type in, FirstName. In Access, you can name your table columns and objects almost anything that you like, but you'll save yourself some headaches down the road if you avoid including any spaces or special characters.

A typical convention is to run multiple words together and capitalize each word in your names, like I've done here. At this point, I'll press the Tab or the Enter key, which will jump me to the next column and I'll repeat the process to enter a last name field. You can keep your hands on the keyboard using the arrow keys to move up and down in the list or you could just press the underlined shortcut character that corresponds to the data type that you want. For instance, to choose Short Text, I'll just type in the T character. Then I'll type in the field name of LastName.

Let's turn our attention to the ribbons now. You'll notice that we have two new additional tabs here at the very top. Both of these are under the heading of Table Tools and we have one called Fields and one called Table. In the Fields tab, this will allow us to switch our view over to design view, or create new fields and modify existing fields with these command buttons. Let's create a phone number field instead of using the tab interface down below. Now, you might think that the phone number will use the Number data type, but Number data type should only be used for numerical fields that could be included in mathematical calculations.

Since it wouldn't make any sense to add two phone numbers together, and we want to include punctuation such as the parentheses characters, we're going to store the phone number as a text data type. Once again, I'll choose Short Text from the menu. Then I'll create the third field over here and I can give it a name. I'll just go ahead and type in Phone. At this point, we should save the changes we've made to our table structure. You can come up and click on the disk icon in the Quick Access toolbar, or use the standard Windows Control + S shortcut key to save your object.

It's gonna prompt me for a table name, and I'll call this Guests. I'll press Enter to save the change, and you'll notice that it gets updated over here in the navigation pane for the database. So now our database is beginning to take shape. We have created a table where we can begin to collect information about the guests of the Landon Hotel. Access created a primary key for us in the ID field and we added three text fields to hold the first name, last name, and the guest's phone number to our database.