How To Create A Table In Access 2013 Using Sql

5 Creating and Viewing Tables

Tables are the main units of data storage in Access. Recall that a table is made up of one or more columns (or fields) and that a given column may appear in more than one table in order to indicate a relationship between the tables.

From the business example discussed earlier, we concluded that two tables would be sufficient to store the data about Customers and their bank Accounts. We now give the step-by-step instructions for creating these two tables in Access.

There are a number of ways to create a table in Access. Access provides wizards that guide the user through creating a table by suggesting names for tables and columns. The other main way to create a table is by using the Design View to manually define the columns (fields) and their data types.

While using the wizards is a fast way to create tables, the user has less control over the column names (fields) and data types. In this tutorial, we will describe the steps to create a table using the Design View. Students are encouraged to experiment on their own with using the Create Table wizard.

5.1 Creating a Table Using the Design View

To create a table in Access using the Design View, perform the following steps:

In Access 2007, 2010 and 2013, the Create New Table tab should already be highlighted and a new table named table1 created. If this is not the case, click on the Create tab and click on the Table icon. Then pull down the View menu and choose Design View.

The Table Design View will appear. Fill in the Field Name, Data Type and Description for each column/field in the table. The CustomerID field is filled in below:

Table Design View for Access 2007

Note that the default name given for the table is Table1. In a later step, we will assign an appropriate name for this table.

Fill in the information for the fields as follows:

Field Name	Data Type	Description
CustomerID	Number	The Unique Identifier for a Customer
FirstName	Text	The First Name of the Customer
LastName	Text	The Last Name of the Customer
StreetAddress	Text	The Street Address of the Customer
City	Text	The City of the Customer
State	Text	The home State of the Customer
Zip	Text	The Zip Code of the Customer

A figure showing the design view with the new table definition filled in is given below:

The next step is to define the Key for the table and to save the table.

Now that all of the fields have been defined for the table, a Primary Key should be defined. Recall that the Primary Key will be used to uniquely identify a record in the table (in this case a Customer). Highlight the CustomerID field and click on the Primary Key button on the button barNotice that a small key appears next to the field name on the left side.Note: To remove a primary key, simply repeat this procedure to toggle the primary key off.
As a final step, the table must be saved. Pull down the Office menu and choose the Save As menu item. A dialog box will appear where the name of the new table should be specified. Note that Access gives a default name such as Table1 or Table2. Simply type over this default name with the name of the table.For this example, name the table: Customer Then click on the OK button.

At this point, the new Customer table has been created and saved.

A Note about naming fields (Columns) in MS Access

When defining the fields (columns) for a table, it is important to use field names that give a clear understanding of the data contents of the column. For example, does the field CNO indicate a Customer Number or a Container Number ?

Field names in Access can be up to 64 characters long and may contain spaces. However, the use of spaces in field names and table names is strongly discouraged . If you wish to make field names easier to read, consider using an underscore character to separate words. However be certain no spaces appear before or after the underscore.

The following table summarizes some different ways to give field names:

Description	Bad	Good
Unique identifier for a customer	CID	CustomerID or Customer_ID
Description for a product	PDESC	ProductDescription
Employee's home telephone number	Employee_home_telephone_number	HomePhone
Bank account number	BA#	AccountNumber

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17