Creating Tables in Microsoft Access 2003

Creating Tables in Microsoft Access 2003
Page content

The first part of creating any database program is the tables. The tables are very important because they hold the data. All of the other components (reports, forms and queries) are all luxury items in a database program; the tables are a necessity. This is why they need to be set up first.

In each table, there are columns or fields. The columns keep the data in a categorized way. For instance, a text column named “First Names” collects first names while a number column called “Phone Numbers” holds all of the phone numbers. This keeps the data consistent and organized, which makes it easier to reference or search later in the application. A useful tip for creating your own database or spreadsheet: Never put two different types of data in the same column. It takes away from the integrity of your data and makes it as useful as keeping the data in a bucket full of water.

The rows in the table are also referred to as records. This is where the individual and specific pieces of information are sorted. For example, while the column is named “First Name”, the information in the rows will have values like “Jim, Jane, or Robert”. This is how the forms will store the values as well.

Before getting in and setting up the tables, you will need to remember some other pieces of information first. For one, database objects are classified as tables, forms, queries, reports or modules. Another thing to know is that data is case sensitive; this means that John and john are not the same. This is a very useful piece of information to remember to avoid storing redundant data.

First, be sure to open a new instance of Microsoft Access 2003. After it has opened, click on the Create New Database link (see picture below), then Blank Database on the right hand side. Before creating a new database you will be asked to name and save it first. This is because whenever new information is added to an Access database, it is saved automatically. You will not have to click the save button every time you enter in a record.

PM 2003 Blank

Name the database “PM Database System” and click Save. Next, you will see a window similar to the one in the picture below:

PM 2003 Save

Click on Create table in Design View from the window. If you want, you can maximize this window. A form comes up that have three headings; File Name, Data Type and Description. At the bottom of the window, you will see more options that you will need later. Right now, start typing the names of the columns in the Field Name column and select a data type from the Data Type field. Use the information below to create the first table. Note: The name of the field is listed first with its data type in the parenthesis right after.

Field Name (Data Type)

ID (AutoNumber)

Company (Text)

Last Name (Text)

First Name (Text)

Email Address (Text)

Job Title (Text)

Business Phone (Text)

Home Phone (Text)

Mobile Phone (Text)

Fax Number (Text)

Address (Text)

City (Text)

State/Province (Text)

Zip/Postal Code (Text)

Country/Region (Text)

Webpage (Hyperlink)

Notes (Text)

Attachments (Attachment)

After you have finished adding all of the fields, click the Save button. Save the table as “Employees” and close the form. A dialog box will appear asking if you will like Access to set a primary key, click Yes. You will notice that the ID field is now your primary key.

There you have it! You’ve set up a table in Access 2003.