Create a New Table in Access

Before we start building a table in Access, we should take a minute and think through what information we want to track. What information will we need about each department?

One item we will definitely want to include is the DeptCode field. This field is what will allow us to connect our department information with the faculty table that we already have. It would also make sense to include a department name field that's more descriptive. For example, the BIOL department represents the Biology department. We might also want to include fields for the location on campus, the phone number, department email address, and the departmental web site. For the departmental web site, we will make use of the hyperlink data type. Hyperlinks allow you to enter a web address that can easily be opened by clicking.

Let's begin by setting up a new table.

Step1. To switch to the Create command tab, on the Ribbon,

Click the Create tab

Step2. To create a new table, in the Tables group,

Click

A new, empty table opens in the object workspace. The first field is automatically created and titled ID. We will discuss this field in a bit. First, we'll enter a few fields of our own.

Step3. To create the first field,

Click

Step4. To choose the field type, in the drop-down menu,

Click Short Text

The name of the field is now highlighted, so we can type our field name directly.

Step5. To enter a name for the field, in the Field1 area, type:

DeptCode Tab key

The drop-down menu for the next field's type opens automatically.

Step6. To choose the field type, in the drop-down menu,

Click

Step7. To enter a name for the field, in the new column heading, type:

DeptName

We can also jump directly to the drop-down menu by using the mouse. Let's try that next.

Step8. To select a data type for the next column,

Right-Click

Step9. To set up the field, in the drop-down menu,

Click, type: Office

Step10. For the rest of the fields, enter the following information:

NOTE: Like EmployeeID, the Phone field is considered a text field, even though it is made up of numbers, because we will never perform any calculations on it.

Setting the Field Size

Now that we have the basic field names and their types established, we can go back and set up some additional information for each field. The first thing we'll do is edit the field size.

The default field size for Short Text is 255 characters, but we don't expect any of our Short Text fields to need that much space. If we leave the field size at the default, Access will be reserving a lot of space for our database that will never be needed. We're going to put some limits in so that there is less unused space in our file.

Step1. To select the DeptCode field,

Click DeptCode

Step2. Click the Table Fields tab, if necessary.

In the Properties group, we can see the default field size:

NOTE: Depending on your screen resolution, the icons on the Ribbon may appear differently.

Step3. To adjust the field size, in the Field Size field on the Ribbon,

Click 255, type: 4 Enter key

We know our department codes are always 4 characters, so we don't want to allow any entries longer than that. This will prevent anyone from accidentally entering in a department code of longer than 4 characters.

Step4. To move to the next field,

Click DeptName

We will be putting the full name of the departments in this field, so we'll want to allow for more characters than 4, but we shouldn't need 255.

Step5. To adjust the field size of the DeptName field, in the Field Size field on the Ribbon,

Click 255, type: 50 Enter key

Step6. Use the following information to establish the field size for the rest of the fields:

Remember, the Office field only needs 6 characters because all offices at the University of the Midwest have a 6-character code. Similarly, at this university all email user names are limited to 8 characters. As with the Phone field in tblFaculty, the field is 14 to account for parentheses around the area code, a space, and a hyphen for a typical number and area code.

Our last field, URL, will be a hyperlink. If we click on that field, we'll see that there is no field size entry possible.

Now that we have our field sizes established, let's enter in some data.

Entering Data in a Table

We'll start by entering some information into our newly created table by hand, to practice direct data entry.

Step1. To start entering the first record,

Click the first cell in the DeptCode column, type: pols, Tab key

NOTE: We will adjust the settings for this field later to display our entry in all capital letters.

Step2. To enter the department name, in the DeptName field, type:

Political Science Tab key

Step3. To enter the location, in the Office field, type:

SM211 Tab key

Step4. Use the following information to finish data entry for this record:

Someone else has entered some additional department records into Excel for us, so we will import the remaining entries. Before we can begin importing records into our table, we must close it. Access will not allow us to import records into an open table. Let's do that now.

Step5. To close our new table,

Right-Click, Click Close

Access asks us if we want to save the design of our table. We've worked on data types and sizes, so we do want to save this information.

Step6. To begin saving the table,

Click

Because we have not given this table a unique name yet, we are given the opportunity to do so now. We'll use standard Access naming conventions, which means we'll add the prefix "tbl" to the name of the table. This will make it easy to find our table in the navigation pane, whether we have our database objects listed alphabetically, or by type.

Step7. To change the name of our table, in the Save As dialog box, type:

tblDepartments Enter key

Our new table closes, and in the navigation pane, we now see two objects:

Importing Records from Excel

Now that we have our tblDepartments closed, we can import the remaining records.

This section has less guidance than the previous sections. If you would like a walk-through, watch the video Importing Records from Excel.

Step1. Click the External Data tab, then in the Import & Link group, use the New Data Source button to import an Excel file.

Step2. Browse to the Departments_to_Import.xlsx file, and append the records to tblDepartments.

Step3. Open tblDepartments to confirm that 4 additional records have been added.

Enhancing Table Design

Now that we have all of the relevant records in tblDepartments, we can do a few things to improve the table. We will set a primary key, and set up an input mask for the Phone field.

Setting the Primary Key

When we first created our table, Access created an Autonumber field called ID in the first column, and set it up as the primary key. This field will automatically assign a unique number to each record as it's entered. For situations when the records in a table do not have a good candidate for a primary key, this can be very helpful. However, in our table, we already have a field that is the perfect candidate for primary key, the DeptCode, since this code is unique to every department.

What we'll do now is move to design view to delete the ID field, and assign DeptCode as our new primary key.

NOTE: If you did not successfully complete the challenge exercise, open MidPoint1.accdb from the MidPoint files folder in Access-The Basics, and save the database as UofMW.accdb.

Step1. Reopen tblDepartments, if necessary.

Step2. Switch to the Home command tab on the Ribbon, if necessary.

Step3. To switch to design view, in the Views group,

Click

NOTE: You can also change to design view from any tab by using the view buttons in the lower-right corner of the object workspace:

In design view, we can see that the Autonumber field is set as the primary key:

Let's try deleting this field now.

Step4. To select the entire ID field's row, in the Field Name area,

Click

Step5. To delete the field, on the keyboard, press:

Delete key

We see a warning from Access:

6. To confirm deletion of the field,

Click

Access warns us again, this time because the field we are trying to delete is set as the primary key.

Step7. To confirm deletion of the primary key,

Click

The ID field has successfully been deleted, and no field is selected as the primary key.

Step8. To select the DeptCode field, if necessary,

Click

Step9. To set the DeptCode field as the primary key, in the Tools group of the Design command tab on the Ribbon,

Click

The primary key for our table is now correctly set to the DeptCode field.

Step10. Save the changes.

While we're in design view, let's set up the Phone field so that phone numbers will be displayed in a more user-friendly way.

Using an Input Mask for the Phone Number

We could leave the Phone field as it is, but this would display phone numbers as an unbroken string of ten numbers. This can be difficult to read. Instead, we will tell Access to only accept numbers for data entry, and to display our phone numbers with parentheses and other characters that will make them easy to read and recognize.

The way to accomplish this is to set up an input mask. Input masks make data entry more precise and error-free, as they restrict the type of data that can be entered. They can also make data more readable by automatically adding familiar symbols. For common types of data, Access has input masks pre-programmed. We simply have to choose the right mask.

Step1. To select the Phone field, in the Field List,

Click Phone

We will set up our input mask using the Field Properties area, which is below the Field List.

There are many properties we can adjust from this area, including the field size. We will focus on the Input Mask field.

Step2. To position the cursor, in the Field Properties area,

Click Input Mask

Someone who knows the proper code could simply type in the input mask directly, but we'll make use of the Input Mask Wizard.

Step3. To launch the Input Mask Wizard, on the far right edge of the Input Mask field,

Click

The Input Mask Wizard opens:

We see a list of several pre-programmed input masks that we can choose from, and the one we want is already selected.

Step4. To continue,

Click

The next screen in the wizard shows us what the input mask code will be:

In addition to the parentheses, space, and hyphen, we see 9s in the place of the area code, and 0s in the place of the local number. This code limits input to numbers, and makes the area code optional.

NOTE: If we wanted to make the area code required, we could replace the 9s with 0s between the parentheses.

Step5. To continue,

Click

The next step in the wizard allows us to choose whether to store the data with or without the symbols in the mask. When we created this field, we set the field size for 14 in anticipation of needing room for these extra symbols.

Step6. To keep the symbols stored with the phone number,

Click the "With the symbols in the mask, like this:" radio button

Step7. To continue,

Click

We see the finish screen.

Step8. To continue,

Click

We are returned to design view, and the correct code is in place in the input mask field. The following table explains what some of the numbers and symbols in the Input Mask field represent:

Let's go back to datasheet view to see our data.

Step9. To switch to datasheet view, on the Ribbon,

Click

Access reminds us that we can't switch out of design view until we save the changes that we've made.

Step10. To save our design changes,

Click

We are returned to datasheet view. Now, in the Phone field, our phone numbers are displayed with an improved appearance. In addition, because we changed our primary key to DeptCode, our records have been sorted in alphabetical order by department code. Let's look at changing another input mask next, this time for the department code.

Using an Input Mask for Text

Right now, the DeptCode field is not uniform. The last record is in lowercase letters, whereas the other records are all in uppercase. We'd like DeptCode in tblDepartments to look like it does in tblFaculty, where each code is shown in uppercase. We can do this with an input mask. In this case, there is nothing in the Input Mask Wizard that will help us, so we'll enter the code in manually.

Step1. To return to design view,

Click

We return to design view.

Step2. To select the DeptCode field, in the Field List,

Click DeptCode

We'll select the Input Mask field again, but instead of launching the Input Mask Wizard, we'll type in a code to get the results we want.

Step3. To select the correct area, in the Field Properties,

Click Input Mask

Our cursor is blinking in the Input Mask field. We will access online Help to find some information regarding input masks.

Step4. To open the online help documentation, on the keyboard, press:

F1 key

NOTE: The F1 key opens contextual help links, based on where your cursor is. You can also search for Help topics by clicking the Help tab in the Ribbon and then clicking the Help button, or by clicking "Tell me what you want to do..." on the Ribbon.

A browser window will open, and the page "InputMask Property, Access" from the Microsoft Docs site will load.

Step5. To choose the correct link, if necessary,

Click the "TextBox.InputMask Property (Access)" link

We see some general information about the Input Mask property. Following are some additional codes for input masks that are helpful for requiring letters or numbers, and changing their case:

If the University of the Midwest had some departments with just three letters, we might use "LLL?" to allow the last letter to be optional. In our case, we want to require the DeptCode entries to be exactly four letters. The code for that is "LLLL".

In addition, we want to make sure DeptCode is always shown as capital letters. We'll need to add the ">" character at the beginning to force that to happen.

NOTE: A field can also be left empty, regardless of the input mask codes, unless it is marked "required."

Step6. Close the browser window.

We return to the Access window.

Step7. To set the Input Mask for DeptCode, in the Input Mask field, type:

>LLLL

Let's see if this fixes the DeptCode field entries. First, we'll save our changes.

Step8. Save the changes we've made to the design of our table.

Step9. To switch to datasheet view, on the Ribbon,

Click

We now see that POLS in the last record is in all uppercase.

We are done with tblDepartments, so let's close it now.

Step10. To close the table,

Right-Click, Click Close

Now that we have our two tables formatted properly, in the next section we will set up a relationship between them in our database.