Create Tables in Access – Tutorial
How to Create Tables in Access: Overview
This lesson shows you how to create tables in Access. A table is an organized structure that holds information. It consists of “fields” of information into which you enter the “records” of the table. A field is a single column within a table, consisting of one category of information. A record is a collection of related fields that describe a single item, contained in a row within a table.
One way to create tables in Access is by creating the tables in “Design View.” To create tables in Access in “Design View,” click the “Create” tab in the Ribbon. Then click the “Table Design” button in the “Tables” group. Doing this then shows the new table in the tabbed documents area.
In “Design View,” you will not see the actual data stored in your table. You will only see a representation of the structure of the table. This gives you more control over the properties of the fields versus using “Datasheet View” to create a table.
This window is divided into two panes. First, there is the “design grid” at the top, where you enter field names and data types. Then there is the “properties” section beneath it. In the “design grid” at the top of the table design view, there is a small box at the far left end of each field. This is the “row selector” button. You can click this small square to select the entire row. You need to do this frequently in Access, so note the location of this object now.
Create Tables in Access – Tutorial: A picture of a user creating a new table and assigning a data type to a field in Design View in Access 2016.
In “Design View,” you create tables in Access by typing the field names into the “Field Name” column. Field names must be unique within a table, and should be brief, yet descriptive. You should also consider not placing spaces within the field names. If you want, you can adopt a convention such as capitalizing the first letter of each word in a field name, or using the underscore character instead of a literal space between words in a field name. Also, the order of fields in this column is the order they appear from left to right in “datasheet view” of the table. “Datasheet view” is the view that lets you see the actual data in the tables.
Next, for each field you create when you create tables in Access, you must assign it a data type. To do this, use the drop-down that appears when you click into the “Data Type” column to the right of the field name. When you create tables in Access, each field in the table must have a data type assigned to it. This tells Access what kind of data you will be storing in the field. “Short Text” is default data type for new fields in Access 2013 or later. In relational databases, the more varied kinds of data types that exist within a table, the quicker it is to index and query those tables. So, feel free to change the type, as needed. Review the various data types you can assign to fields in tables.
Field Data Types You Can Assign When You Create Tables in Access 2013 or later
|Data Types in Access|
|Short Text||Contains text, or a combination of text, numbers, and other information. Maximum length is 255 characters in length. When you create tables in Access in “Design View,” this is the default field type assigned to new fields.|
|Long Text||A longer version of a text field. Maximum length is 65, 535 characters.|
|Number||Can contain only numeric data on which you want to perform calculations, NOT phone numbers or zip codes. As you do not perform calculations with these numbers, they are text fields.|
|Date/Time||Contains a date or time code. Useful for Date/Time calculations.|
|Currency||Similar to the Number data type in function, but formatted as currency. Uses fixed point calculation, which is faster than the floating point calculation used in Number fields.|
|AutoNumber||Assigns a unique numeric ID to all records entered in the table. Useful as a primary key field.|
|Yes/No||Stores Logical data types: “Yes/No,” “True/False,” “On/Off,” “-1/0.” Used when only two possible values in a field can exist.|
|Connects to objects in Windows applications. You can use OLE Object data types for ActiveX objects, pictures, calendars, and other types of files. Only used in Desktop database files.|
|Hyperlink||Contains a hyperlink to an address on the World Wide Web.|
|Allows you to attach any type of supported file, such as images, or spreadsheets, for example. Provides greater attachment flexibility than the OLE Object field and also uses storage space more efficiently than OLE fields do.|
|Calculated||Allows you to create a calculated field, which contains a value that is derived by performing a function on other table fields using an expression that you create.|
|Lookup Wizard…||Guides you through setting up a lookup field, which will then contain values from another table, query, or values that you enter by hand. Useful for combo boxes and list boxes in forms.|
(Web App Only)
|Used to store picture data. Only used in web apps.|
Create Tables in Access – Tutorial: A picture of a table in Design View in Access, showing field names, data types, and a primary key.
Below the design grid is the “Field Properties” section where you set the properties of the currently selected field in the table. In this area, the properties of the currently selected field are displayed the “General” and “Lookup” tabs. You can edit or set the field’s properties here by changing values shown, as needed.
When finished, you simply need to set the primary key for the table and then save it. The primary key field is the field that uniquely identified each table record. Often, this field exists naturally in the data. If not, you can add an “AutoNumber” field to a table to create a primary key field. To mark a field as a primary key field, click the row selector at the left end of the row that contains the field you want to set as the “primary key” for the table. Then click the “Design” tab within the “Table Tools” contextual tab in the Ribbon. Then click the “Primary Key” button in “Tools” group.
To save the table when you are finished, click the “Save” button in the Quick Access toolbar. Then type a name for the new table into the dialog box that appears. Then click the “OK” button.
Create Tables in Access Using “Design View”: Instructions
- To create tables in Access using “Design View,” click the “Create” tab in the Ribbon.
- Then click the “Table Design” button in the “Tables” group.
- A new table then appears in the tabbed documents area.
- Type the name of a field into the “Field Name” column.
- Then press “Tab” on your keyboard to move to the next column to the right.
- Then use the drop-down menu in the “Data Type” column to assign the field a data type.
- Press “Tab” on your keyboard to move to the “Description” column.
- If desired, type a description of the data stored in this field.
- Then press “Tab” on your keyboard to move down to the next row.
- Repeat steps 4 through 9 until you have created all of the necessary table fields.
- Click the row selector at the left end of the row that contains the field you want to set as the “primary key” for the table.
- Click the “Design” tab within the “Table Tools” contextual tab in the Ribbon.
- Then click the “Primary Key” button in “Tools” group.
- Click the “Save” button in the Quick Access toolbar.
- Then type a name for the new table into the dialog box that appears.
- Then click the “OK” button.
How to Create Tables in Access: Video Lesson
The following video lesson, titled “Creating Relational Database Tables,” shows how to create tables in Access. This video lesson is from our complete Access tutorial, named “Mastering Access Made Easy v.2016-2013.”