Creating Tables in Microsoft Access

Tables are used to store all of the data in your database.  Before starting, have an idea of what your database will be used for and what kinds of information it will store.  Each table will hold one type of information—such as a list of winners and their contact information, or contests' dates and their winners—which will then be correlated with one another.

 

Creating a Table

• After loading your database in Access, the main database window should pop up.  Under Objects, click on the icon labeled Tables.

• Three options will appear: Create table in Design view, Create table by using wizard, and Create table by entering data.

 

Creating a Table by using a wizard

Using a wizard is probably the easiest way to create a table, though it does not allow you to have as much control over your tables as Design view does.

• Double-click on Create table by using wizard.  The Table Wizard window will pop up.

Access offers a wide variety of options for common types of tables you may want to create, both for Business and Personal use.  The sample fields in these tables are pre-formatted for the expected data type (i.e., date or postal code).  Access will also help you set up relationships between tables based on samples.

• Use the radio buttons to select either the Business or the Personal list.  Scroll through the list of Sample Tables until you find the one you want, then click on it to select it.

• Next, choose the Fields your table will include by selecting them from the list of Sample Fields.  Click on the field name in the list, then click the right arrow button [>] to add it to the list of Fields in my New Table.  To remove a field from your list, select the field name, then click the left arrow button [<] to remove it.  To add or remove all the fields in the Sample Fields list, use the double arrows [>>] or [<<].

• Once you have added a field to your table list, you can rename it by selecting it, then clicking the [Rename Field] button.

• You can even add individual fields from multiple types of sample tables.  Add fields from one table type, then select a different type of table and continue adding fields.

• When you are finished adding fields, click [Next].

• The wizard will prompt you to name your table.  Choose a name to reflect the information that will be stored in the table.

• The wizard also asks whether you want it to set the “primary key” for you.  If you select Yes, it will add another field whose value is set to AutoNumber.  Each record will be numbered consecutively as you add it, beginning with 1 for the first record you enter.  This is the default option, and is useful if you do not already have a numerical identification system for your records.

• Select No if you already have a primary key for each record.  For example, if you are making a table listing students, you may want to use student ID numbers for the primary key.  A business may list employees by their Social Security numbers, or products by product code numbers. 

• When you are finished, click [Next].

• If you already have at least one other table, the wizard will ask you if the new table is related to any other table in your database.  If you are basing your table on one of its samples, Access will suggest relationships between tables it expects to be related.  See the next manual page, Managing Relationships in Access, for more information about relationships.

• Click [Next] again.  The wizard will ask you what you want to do with the table next.  If you want to keep working with the table design, select Modify the Table Design.  If you want to begin entering data, select Enter Data Directly into the Table.  If you want to use a form to enter data, select the final option.

• Click [Finish] to create your table.

If you chose to enter data directly, the table will open as a spreadsheet. 

• You can begin entering data into a cell by clicking on the cell, then typing the data.  Use the mouse, the arrow keys, or [Tab] to switch between cells.  If a cell reads “AutoNumber,” you do not need to enter data into that cell.  Access will automatically enter a number into it once you complete the other information in the record.

 

Creating a Table using Design View

Design view allows you much more control over the contents and appearance of your table, but is also more complicated to use.  Make sure you understand the use and creation of tables, as well as the various types of data a table can hold, before you try to create one in Design view.

• You can open an existing table in Design view by right-clicking its name in the Database window, then selecting Design View from the pop-up menu.

• To create a table in Design view, double-click that option in the Database window.  The blank table will open in Design view.

• Now you can set up each field in your table.  Click the first cell under Field Name.  Enter the name of your first field by typing it in.  In your table, this will appear as the first column of data, often the ID number or primary key.

• Next, select a data type for this field.  Click [Tab], or use the mouse to select the Data Type cell.  Click the small arrow button to drop down a list of possible data types, such as Text, Date/Time, or AutoNumber.  If you already know the data type you want, you can also type it in.

• You can enter a description into the Description column if you need to make notes about the type of data in each field.  This does not affect the way your table functions and is not required.

At the bottom of the window, you will notice more detailed options for each cell.  Advanced users will use these options to control minute aspects of their design, and to perform more advanced functions.

• Keep adding fields in each row until you have finished your table. 

• Make sure you designate one field as the primary key for your table.  Right-click on the field you chose, then click Primary Key on the pop-up menu.

• When you are finished designing the table, be sure to save it.  If you try to close the window, Access will prompt you to save the table, letting you choose a name for it.  It will be saved as part of your database.

 

Creating a Table by Entering Data

The final option essentially takes you straight to the data entry view without having you set up and design the table first.  You can always edit the table design, including setting a primary key and renaming fields, by opening the table in Design view later.