MS Access —

Creating a New Table from Scratch

Creating a table from scratch in Design view.

The is helpful if you're new at building tables, but the more you start using Access, the less you will probably want to use the Table Wizard. That's because you will know exactly what type of tables and fields your database needs and how to create them.

This lesson explains how to build your own tables from scratch. The most straightforward way to build a table is in Design view, where adding fields to a table and specifying their data types is not much different than basic data entry.

  1. Click the Tables icon in the Objects bar if it isn't already selected, then double-click Create table in Design view.

    The new blank table appears in Design view. Now all you have to do is add the fields you want included in the table. The table you will create in this exercise will track telephone calls made to customers. Let's add the first field.

  2. Type Date in the first blank Field Name box.

    Since this field will store the date the call was made, you need to change the data type of the field to Date/Time.

  3. Click the Data Type box next to the Date field.

    A list arrow appears on the right side of the box.

  4. Click the list arrow and select Date/Time from the list.

    The Date field will now only accept date and time information. Let's add the next field to the table...

  5. Press Tab two times and type Contact ID.

    In case you were wondering, the Description box you just tabbed past is used to provide users with online prompts and instructions. Anything you enter in a field's Description box will appear in the Status bar whenever a user selects that field. We'll discuss the Description box in greater depth later on.

    The Contact ID field will indicate the contact that was called. Since the Contact ID field will always be a number, you need to change its data type to numeric. A faster way of changing a field's data type is to type the first letter of the data type in the Data Type box. For example, typing a `D' would change the data type to Date. Table lists these keyboard shortcuts.

  6. Press Tab.

    The Data Type box should be selected. Try typing in the data type this time, instead of using the list arrow.

  7. Type N in the Data Type box.

    "Number" appears in the Data Type box.

  8. Complete the table by entering the following field names and data types:

    Field Name Data Type

    Phone

    Text

    Comments

    Memo

    FollowUp

    Yes/No

    Make sure that you press Tab after each field. If you make a mistake, you can either click the field you want to edit or press Shift + Tab to move back to the previous field. Your table should look like the one in Figure.

    Once you have finished modifying a table, you have to save your changes.

  9. Click the Save button on the toolbar to save your changes.

    Access asks you to give your new table a name.

  10. Type Phone Calls and click OK.

    Access asks if you want to create a primary key now.

  11. Click No and then close the current table and database.

Data Type Shortcuts
Data Type Shortcut Data Type Shortcut

Text

T

AutoNumber

A

Memo

M

Yes/No

Y

Number

N

OLE Object

O

Date/Time

D

Hyperlink

H

Currency

C

Lookup Wizard

L

TO CREATE A TABLE FROM SCRATCH:

  1. IN THE DATABASE WINDOW, CLICK THE TABLES ICON IN THE OBJECTS BAR AND THEN DOUBLE-CLICK CREATE TABLE IN DESIGN VIEW.

  2. TYPE A FIELD NAME IN THE FIELD NAME COLUMN.

  3. PRESS TAB, CLICK THE LIST ARROW IN THE DATA TYPE BOX, AND SELECT A DATA TYPE FOR THE FIELD.

  4. REPEAT STEPS 2 AND 3 AS NECESSARY TO ADD NEW FIELDS TO THE TABLE.

  5. CLOSE THE TABLE WINDOW.

  6. CLICK YES TO SAVE THE TABLE, ENTER A TABLE NAME, AND THEN CLICK OK.

  7. CLICK NO WHEN ACCESS ASKS IF YOU WANT TO CREATE A PRIMARY KEY NOW.