MS Access

Modifying a Table and Understanding Data Types

A table in Design view.

Once you have created a table, you can modify it later in Design view. Design view allows you to change the structure of a table by adding, deleting, and its fields.

Because there are so many different types of data, Access offers several different types of fields. A field's data type determines the type of information that can be stored in a field. Table lists the various available in Access. A field's data type restricts what type of information you can enter in a field. For example, you cannot enter text into a number field.

In this lesson you will modify a table by adding a new field and then determining the field's data type.

  1. Click the Contacts table and click the Design button in the Database window.

    Other ways to modify a table are to open the table and click the View button on the toolbar, or open the table and select View » Design View from the menu.

    The Contacts table appears in Design view, as shown in figure, which allows you to add, delete, or modify the table's structure and fields.

    Here's how to change a field name in Design view:

  2. Scroll down, if necessary, and select the PostalCode field in the Field Names column. Replace the text "PostalCode" with ZipCode.

    That's how easy it is to change a field name. Now try adding a new field to the table.

  3. Press Tab three times.

    The cursor should be located in the blank Field Name box below the ZipCode field. To add a field in Design view, simply type a new field name in any blank Field Name box.

  4. Type Birthday and press Tab.

    By default, the Text data type is assigned to all new fields. Here's how to change a field's data type:

  5. If it is not already selected, click the Data Type box next to the Birthday field.

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

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

    The new Birthday field will now only accept date and time information. The new Date/Time data type also makes your database more flexible and powerful because now you can sort birthdays by date or even use them in a calculationfor example, to determine a person's age.

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

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

    Other ways to save a database object are to press Ctrl + S, or select File » Save from the menu.

    You've finished modifying the table, so...

  8. Close the Contacts table.

Data Types
Data Type Example Description

Text

Legal Name:

John Doe

Stores text, numbers, or a combination of both, up to 255 characters long. Text fields are the most common of all data types.

Memo

Notes:

Sally displays a high amount of...

Stores long text entriesup to 64,000 characters long (the equivalent of 18 pages of text!). Use memo fields to store notes or anything else that requires a lot of space.

Number

Age:

31

Stores numbers that can be used in calculations.

Date/Time

Birthday:

April 7, 1969

Stores dates, times, or both.

Currency

Price:

$84.95

Stores numbers and symbols that represent money.

AutoNumber

Invoice Number:

187001

Automatically fills in a unique number for each record. Many tables often contain an AutoNumber field that is also used as their primary key.

Yes/No

Employed?:

Yes

Stores only one of two values, such as Yes or No, True or False, etc.

OLE Object

Photo:

Stores objects created in other programs such as a graphic, Excel spreadsheet, or Word document.

Hyperlink

Web Site:

www.amazon.com

Stores clickable links to files on your computer, on the network, or to Web pages on the Internet.

Lookup Wizard

Purpose of Trip:

A wizard that helps you create a field whose values are selected from a table, query, or a preset list of values.


To display a table in design view:

  • Open the table and click the view button on the toolbar.

OR...

  • In the database window, click the tables icon in the objects bar, select the table, and click the design button.

To change the data type for a field:

  1. Display the table in design view.

  2. Click the field's data type box, click the list arrow, and select the data type from the list.

To save changes to a database object:

  • Click the save button on the toolbar.

OR...

  • Select file » save from the menu.

OR...

  • PRESS CTRL + S.