MsOffice.US
Home
Excel
FrontPage
Access
Word
PowerPoint
Outlook
Visio
Project
Publisher
InfoPath
Exchange
SharePoint
Commerce
Expression
Silverlight
Management
Software
Windows

  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

Links

www.msoffice.us

dbmanagement.info

italiankitchenrecipes.com

relationshipadvices.info

www.coptics.info

 

 

 

 

 

   

 
Home Access Topics

Creating a Table with the Table Wizard

 


 

In this lesson, you learn how to create a table by using the Table Wizard.

Tables Are Essential

As discussed in Lesson 1, your tables really provide the essential framework for your database. Tables not only hold the data that you enter into the database, but they are designed so that relationships can be established between the various tables in the database. Tables can be created from scratch, as discussed in the next lesson, or they can be created using the Table Wizard.

Working with the Table Wizard

The Table Wizard can save you a lot of time by supplying you with all the needed fields and field formats for entering your database information. Access provides a large number of different kinds of tables that you can create with the wizard. The wizard is also fairly flexible, allowing you to select the fields the table will contain and the way in which they will be arranged. You can also change the name of a field during the process. If the wizard doesn't provide a particular field, you can always add it to the table later, as discussed in Lesson 5, "Editing a Table's Structure."

To create a table using the Table Wizard, make sure you've opened a database as described in Lesson 2 and then follow these steps:

1.    In the database window, click the Tables object icon, and then double-click the Create Table by Using Wizard icon. The Table Wizard opens.

2.    On the first Table Wizard screen, you can select from two categories of table types: Business or Personal. Your choice determines the list of sample tables that appears (see Figure 3.1).


 

Figure 3.1. Select either the Business or Personal category to view a list of tables.

3

3.    Select a table in the Sample Tables list; its fields appear in the Sample Fields list.

4.    To include a field from the Sample Fields list in the table, select the field and click the Add (>) button to move it to the Fields in My New Table list. You can include all the fields in the Sample Fields list by clicking the Add All (>>) button.

5.    If you want to rename a field that you have added, click the Rename Field button, type a new name into the Rename Field box, and then click OK.

6.    Repeat steps 3 and 4 as needed to select more fields for the table. You can select fields from more than one of the sample tables for the table that you are creating (remember that you want fields in the table related only to a particular theme, such as customer information). When you're finished adding fields, click Next to continue.

7.    The next screen asks you to provide a name for the table (see Figure 3.2). Type a more descriptive name if necessary to replace the default name.


 

Figure 3.2. Provide a name for the table and allow the wizard to select a primary key for the table.

3

8.    This dialog box also asks whether you want the wizard to create a primary key for the table or allow you to select the primary key yourself. For example, CustomerID is an excellent primary key because each customer is assigned a different ID number. In this case, click Yes, Set a Primary Key for Me to have the wizard choose your primary key field. You can learn to set your own primary keys in Lesson 4, "Creating a Table from Scratch."

TERM

Primary Key The field that uniquely identifies each record in the table. Every table must have a primary key. This is usually an ID number because most other fields could conceivably hold the same data for more than one record (for example, you might have several people with the last name of Smith).

9.    Click Next to continue. Because you're allowing the wizard to select the primary key, you are taken to the last wizard screen. On the last wizard screen, you have the options of modifying the table's design, entering data directly into the new table, or having the wizard create a data entry form for you. To see the table the wizard created, go with the default: Enter Data Directly into the Table (see Figure 3.3).


 

Figure 3.3. After completing the table, you can have the wizard open it so that you can enter data.

3

10.                    Click Finish.

The new table appears in the Access workspace (see Figure 3.4). From here you can enter data into the table, the specifics of which are discussed in Lesson 6, "Entering Data into a Table." When you close the table, it appears in the Object pane of the database window (you must also select the Tables object icon).

Figure 3.4. Your new table appears in the Access workspace when you close the Table Wizard.

3

 

 Creating a Table from Scratch

In this lesson, you learn how to create a table in Table Design view.

Creating Tables Without the Wizard

Although the Table Wizard provides an easy method for quickly creating tables, it does not provide you with complete control over all the aspects of creating the table's structure. It does allow you to select the fields used in the table from a set list, but it restricts you to only those predefined fields (there are also several types of fields, each used for a different data type).

When you work with tables you work in two different views: the Datasheet view and the Design view. The Datasheet view is used to enter, view and edit data. The Design view is available to create and edit a table's structure. Creating tables from scratch in the Design view allows you to build the table from the bottom up and gives you complete control over all aspects of the table's design.

TERM

Design View This view allows you to enter field names, select the data type that a field will hold, and customize each field's properties. A Design view is available for all the Access objects, including tables, forms, queries, and reports.

 

The Design view isn't the only way to create a table from scratch in Access. You can also create a table in the Datasheet view by labeling your field columns directly on the table's datasheet; this method is similar to creating a worksheet in Excel. We will take a look at both methods for creating a new table.

TERM

Datasheet View This view places each record in a separate row and each field in a separate column (column headings are provided by the field names). This view is used to enter data directly into the table. You will use the Datasheet view whenever you want to view the records in the table or add or edit records.

 


 

Creating a Table in Table Design View

When you create a table in the Design view, you are creating the structure for the table; you create a list of the fields that will be in the table. You also select the data type for each field. (Fields can hold text, numbers, even graphics—you learn the types of fields that can be created later in this lesson.) You also have the option of entering a description for each field. Field descriptions are useful in that they provide a quick summary of the type of data that goes into the field.

Another issue that relates to creating a table in the Design view (or editing a table's structure in the Design view) is that any changes you make must be saved before closing the table. If you have worked in other applications, such as Word or Excel, you might think that saving your work is just common sense. However, when you actually start working on entering data into a table or a form, Access automatically saves your records as you enter them. Therefore, in Access, you need to remember to save only the changes that you make to the structure of a table, form, query, or report. You learn more about this in Lesson 5, "Editing a Table's Structure."

To create a table in Table Design view, follow these steps:

1.    In the database window (of any database) click the Tables icon if necessary, and then double-click Create Table in Design View. The Table Design view opens (see Figure 4.1).

Figure 4.1. The Table Design view allows you to create the structure for your table.

4

2.    Be sure that the insertion point is in the first row of the Field Name column. Type the field name for the first field in your table. Then, press Tab or Enter to move to the Data Type column.

3.    When you move to the Data Type column, an arrow appears for a drop-down list. The default data type setting is Text; several other data types are available, such as AutoNumber, which automatically numbers each of your records (incrementally). This field type is excellent for customer number fields or employee ID fields. Click the Data Type drop-down list and select a field type that suits your needs. The different data types are discussed later in this lesson, in the section "Understanding Data Types and Formats."

4.    After selecting the data type, press Enter to move to the Description column; type a description for the field. (This is optional—the table will work fine without it—however, describing the fields reminds any user of the database what type of information should go into that particular field.)

5.    Enter other fields and their field types (descriptions are optional) as needed. Figure 4.2 shows the structure for a table that will be used to enter product information.

Figure 4.2. A table's structure consists of several fields; fields may differ by field type.

4

Setting the Primary Key

An important aspect of table structure design is that each table must have a field that is used to uniquely identify the records in the table. This field is called the primary key. Setting an appropriate key is trickier than it seems because no two records can have the same key value. In a table of customers, for example, you might think the Last Name field would be a good key, but this theory falls flat as soon as you have more than one customer with the same last name. A more appropriate primary key for your customers is a Social Security number (although people don't like to give these out) because it uniquely identifies each customer.

A good general rule is to create an identification field, such as a customer number, that allows you to assign a sequential number to each customer as you add them to your database table. Access can even help you out with the assigning of numbers to the customers because you can make the field type for the Customer Number field AutoNumber. An AutoNumber field type assigns a number to each record starting with the number 1.

To set a primary key, follow these steps:

1.    In Table Design view, select the field that you want for the primary key.

2.    pkSelect Edit, Primary Key, or click the Primary Key button on the toolbar. A key symbol appears to the left of the field name, as shown in Figure 4.3.

Figure 4.3. The primary key field is marked by a key symbol.

4

3.    saveAfter you select the primary key and have finished entering your table fields, you should save the table. Click the Save button on the Table Design toolbar to open the Save As dialog box.

4.    Enter a name for the table, and then click OK.

5.    dataviewAfter saving the table, you can either switch to the Datasheet view (to enter data) by clicking the View button on the toolbar, or you can choose to close the table by clicking the table's Close (X) button.

Understanding Data Types and Formats

To assign appropriate data types to the fields you create in a table, it is necessary for you to know what differentiates the different data types available for use with your table fields. When you create a field, you want to assign it a data type so that Access knows how to handle its contents. The following are the different data types you can choose:

·        Text— Text and numbers up to 255 characters (numbers that are not going to be used in calculations).

·        Memo— Lengthy text.

·        Number— Numbers used in mathematical calculations.

·        Date/Time— Date and time values.

·        Currency— Numbers formatted for currency.

·        AutoNumber— Sequentially numbers each new record. Only one AutoNumber field can be placed in a table. This field type is typically used for the primary key field.

·        Yes/No— Lets you set up fields with a true/false data type.

·        OLE (Object Linking and Embedding)— A picture, spreadsheet, or other item from another software program.

·        Hyperlink— A link to another file or a location on a Web page. This field type lets you jump from the current field to information in another file.

·        Lookup Wizard— This field type chooses its values from another table.

In addition to a field type, each field has other formatting options you can set. They appear in the bottom half of the dialog box, in the Field Properties area. The formatting options change depending on the field type; there are too many to list here, but Table 4.1 shows some of the most important ones you'll encounter.


 

 

Table 4.1. Formatting Options for Data Types

Formatting Option

Description

Field Size

The maximum number of characters a user can input in that field (applies only to text fields).

Format

A drop-down list of the available formats for that field type. You can also create custom formats.

Decimal Places

For number fields, you can set the default number of decimal places that a number shows.

Default Value

If a field is usually going to contain a certain value (for example, a certain ZIP code for almost everyone), you can set that as the Default Value option. It always appears in a new record, but you can type over it in the rare instances when it doesn't apply.

Required

Choose Yes if a particular field is required to be filled in each record.

The best general rule for setting the data type for the field is to take a moment to consider what kind of data will go into that field. For example, if you are working with the monetary value of a product, you will probably want to use currency.

The different formatting options provided for a field in the Field Properties box are often used to help make sure that data is entered correctly. For example, the Field Size option can be used to limit a Number data type field to only a single or double digit. In the case of the default value, you can actually save data entry time because you use this option when a particular field almost always has a certain value or text entry.

Creating a Table in the Datasheet View

After you feel comfortable creating new tables in the Design view, you might want to dive right in and create tables in the Datasheet view. Creating tables this way immediately creates a table with 20 field columns and 30 record rows. This method still requires, however, that you enter the Table Design view to specify the key field, the field data types, field descriptions, and any field property changes.

Creating tables in the Datasheet view is really useful only if you feel the need to quickly enter some data into the table before setting up the table's properties. To create a table in the Datasheet view, follow these steps:

1.    In the database window (with the Table icon selected), double-click Create Table by Entering Data. A new table in Datasheet view appears in the Access workspace (see Figure 4.4).

Figure 4.4. Tables can be created in the Datasheet view.

4

2.    To enter the field names, double-click any field column heading (Field1, Field2, and so on). Then, type in the field name.

3.    After you have placed the field names, you can begin entering data.

Creating a table in the Datasheet view might be fine for quickly entering data, but you will still probably need to switch to the Table Design view at some point and set up the various field data types and properties.

designviewYou can switch to the Design view from the Datasheet view by clicking the View icon on the Table Datasheet toolbar. Remember to save any changes to the table's design that you make in the Design view.

List of our Access topics 

(Access 2000) An Introduction to MS Access
(Access 2000) Introduction to Ms Access
(Access 2000) Using MS-Access 2000 & Oracle 9i
(Access 2003) Adding Special Controls to Forms
(Access 2003) Adding Special Controls to Forms
(Access 2003) Creating a New Database
(Access 2003) Creating MySQL database
(Access 2003) Creating a Simple Form
(Access 2003) Creating a Simple Query
(Access 2003) Creating a Simple Report
(Access 2003) Creating a Table with the Table Wizard
(Access 2003) Creating Queries from Scratch
(Access 2003) Creating Relationships between Tables
(Access 2003) Creating the MySQL database
(Access 2003) Customizing a Report
(Access 2003) Data Base and Structured Query Language (SQL)
(Access 2003) Database design
(Access 2003) Editing a Table's Structure
(Access 2003) Entering Data into a Table
(Access 2003) Finding answers to questions
(Access 2003) Formatting Access Tables
(Access 2003) Getting Started
(Access 2003) Guide to Relational Databases
(Access 2003) Identifying tables
(Access 2003) Introduction to data modeling
(Access 2003) Introduction to the SQL language
(Access 2003) Making it easy for the user
(Access 2003) MICROSOFT ACCESS 2003 TUTORIAL
(Access 2003) Modifying a Form
(Access 2003) Printing Access Objects
(Access 2003) Sales statistics
(Access 2003) Searching for Information in Your Database
(Access 2003) Specialized Joins
(Access 2003) SQL joining tables
(Access 2003) SQL Queries
(Access 2003) Taking Advantage of Database Relationships
(Access 2003) Transaction processing
(Access 2003) Tutorials
(Access 2003) Using reports
(Access 2003) Working with dates
(Access 2003) Working with Macros
(Access 2003) Working with the AutoForm Wizard
(Access 2003) Working with the data grid
(Access 2003) Basics of Access presentations ( .ppt)
(Access 2007) advanced_slides .ppt
(Access 2007) Beginning Topics
(Access 2007) Exercises 
(Access 2007) Exporting form to XML
(Access 2007) Getting Started
(Access 2007) Intermediate Topics
(Access 2007) New features and Product Guide
(Access 2007) Part1 Introduction
(Access 2007) Conditional formatting
(Access 2007) Part2 MS ACCESS Tables
(Access 2007) Part3 Relationships in ER Diagram
(Access 2007) Part4 Introduction to MS Access Forms and Reports
(Access 2007) Part5 Working with MS Access Forms
(Access 2007) Part6 Macros, Modules, Data Access Pages and Internet
(Access 2007) Part7 MS Access Queries and SQL
(Access 2007) Part8 Form Customization
(Access 2007) Part9 Working with Subreports
(Access 2007) Part10 Access Switchboards
(Access 2007) Part11 Access customisation options
(Access 2007) Quick Reference
(Access 2007) Report Conditional formatting
(Access 2007) Sample Database files
(Access 2007) Sorting, Filtering, and Indexing Data
(Access 2007) SQL commands to Create Database
(Access 2007) Step by Step
(Access 2007) Tips 
(Access 2007) Programming Access 2007 (Book)
(Access 2007) When to Migrate from Microsoft Access to Microsoft SQL Server
(Access 2007) Advanced Topics
(Access) SOFTWARE SUPPORTING Access
(Access 2007) Advanced topics (Book)
(Access 2010) The Access Object Model
(Access 2010) Tips and Tricks
(Access 2010) Introduction
(Access 2010) New Features
(Access 2010) Upgrading and Converting
(Access 2010) Macros
(Access 2010) VBA Editor
(Access 2010) Naming Conventions
(Access 2010) Security Features
(Access 2010) Extending VBA with APIs
(Access 2010) Creating Classes in VBA
(Access 2010) Working with SharePoint
(Access 2010) Using SQL with VBA
(Access 2010) Using VBA to Enhance Forms.pdf
(Access 2010) Using ADO to Access Data
(Access 2010) Using DAO to Access Data
(Access 2010) Working with the Windows Registry
(Access 2010) Reports with VBA
(Access 2010) Customizing the Office Backstage
(Access 2010) Working with .NET
(Access 2010) Building Client-Server Applications with Access
(Access 2010) Access Runtime Deployment
(Access 2010) Database Security
(Access 2010) VBA Basics
(Access 2010) Designing Advanced Forms
(Access 2010) Designing Advanced Reports
(Access 2010) Programming Microsoft Access
(Access 2010) Access as an Enterprise Platform
(Access 2010) Ms Access SQL Bible
(Access 2010) Create Data Access Pages
(Access 2010) Essential Query Tricks
(Access 2010) Importing and ExportingData.pdf
(Access 2010) Linking Tables with Relationships
(Access 2010) Database concepts presentations (.ppt)
(Access 2010) Working with Controls and Access Objects
(Access 2010) Database Management class presentations ( .ppt)
(Access 2010) Advances Database course presentations ( .ppt)
(Access 2010) Modern Database Management presentations ( .ppt)
(Access 2010) Reserved Words and Special Characters
(Access 2010) Understanding Form Controls

 

Welcome to

MsOffice.us

This is a free website for learning and practicing all the basics of Microsoft Office 2010 training courses, online help, lessons and more...

Links

www.msoffice.us

dbmanagement.info

italiankitchenrecipes.com

relationshipadvices.info

www.coptics.info

  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

 

 

  Home  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking 

  Home  |  Excel  | FrontPage Access Word | PowerPoint  | Outlook | Visio |  Project |  Publisher  |  InfoPath | Exchange  |  SharePoint Commerce  | Silverlight Database  | Data Warehouse  |  SQL  |  OO Design SQL_ServerMYSQL | PHP  | Oracle | VBA ASP.NET  | C programming | Java  |  Photoshop | HTMLDreamweaver | Web DesignExpression Web  | XML  | Java script  |  Security | UML | Flash Silverlight |UNIX | Networking