The University of Texas at Austin

Getting Started with Microsoft Access

17 February 2004
Copyright, Information Technology Services
training@its.utexas.edu
The University of Texas at Austin

Microsoft Access is a versatile relational database program that enables you to efficiently store and retrieve data. This handout explains how to navigate the Access interface, create simple tables and queries, and relate information from one table to another.

Starting Access


1. On the PC, select Start, Programs, and Microsoft Access from the Start list. Microsoft Access Icon
2 Double-click on the icon of any Access database. When you double-click an Access database, Access opens with the database already loaded.

Exploring the Access Interface


Components of the Access Window

Besides the usual window components (close box, title bar, scroll bars, etc.), an Access window has several unique elements identified in the figure below.

The Database Toolbar

The Database toolbar, located beneath the menu bar, has buttons for commonly performed tasks like accessing the Relationships window, adding Objects, Exporting to Office, and other operations. Access let's you customize the toolbar or even display multiple toolbars at the same time. The Standard Access XP toolbar appears undocked in the figure below.

The Data sheet Toolbar

The Data sheet toolbar provides common tasks for editing an object in Data sheet view. The Database toolbar will automatically change into the Data sheet toolbar when this view is selected. The Data sheet tool bar exists between different objects, thus, this toolbar largely remains consistent.

Datasheet Toolbar

The Design Toolbar

The Design toolbar provides common tasks for editing an object in Design view. The Database toolbar will automatically change into the Design toolbar when this view is selected. The Design tool bar exists between different objects, and thus, maintains much of the same functionality.

The Design Toolbar

Common Database Features

Access provides a quick and convenient method of accessing the most common tasks. This includes switching between views, opening and creating new databases, and a few minor formatting issues

Creating a Database


When you start Access there are no databases open, but the Task Pane is visible and provides quick access to common options such as opening an existing database or creating a new database. To create a database, select Blank Database under the New subgroup.

Database Components


An Access database consists of seven different components. These are: tables, queries, forms, reports, pages, macros, and modules. Use the buttons in the database window shown below to create and modify these components. Each component listed is called an object.

Creating Tables


The purpose of a table is to store information. Tables are the building blocks of an access database. Access gives you multiple ways to make tables.

Create a new table

(Step 1) - From the object window select New.

Use the Table Wizard

(Step 2) - Choose Table Wizard. Choose OK.

Add appropriate fields

(Step 3) - Select Employees; double click on the fields that will be in the table. If that isn't the exact name of the field then you can select the Rename Field button and retype the field name. Choose OK . Choose Next .

Change the name of your table

(Step 4) - Type in the table Name and select No, I'll set the primary key . Choose Next .

Set the primary key

(Step 5) - From the drop down list choose the field that will be the primary key. Also, choose the correct data type. Choose Next .

Modify the table design

(Step 6) - Choose Modify the table design. Choose Finish.

Close the table

(Step 7) - Close the table by clicking the X button with a gray background.

Creating Relationships


Relating tables together allows for queries to search in multiple tables. The two most common relationships are One-To-One and One-To-Many.

Click the relationships button

(Step 1) - Select the Relationships button.

(Step 2) - Add both tblEmployees and tblProjects.

(Step 3) - Drag EmpId from tblEmployees and drop it on EmpId from tblProjects.

(Step 4) - Confirm the Relationship type then choose Create.

Ensure the relationship exists

(Step 5) - Confirm the relationship by looking for a line connecting the tables. Close the Relationships window by clicking on the black X with the gray background.

Creating Queries


Queries allow the user to search data from a table(s) and then save that search. Criteria can be specified and saved in a query. There are several types of queries which perform several types of tasks. A select query is a standard query. To create a simple select query, use the Simple Query Wizard.

Create new query

(Step 1) - Select New from the Object Window.

Use the simple query wizard

(Step 2) - Select Simple Query Wizard. Choose OK . (Notice some of the other types of queries available).

Add the requested fields

(Step 3) - Select the table or query you want to query from the Tables/Queries drop down list. Double click on the available fields you want to select. Choose Next .

(Step 4) - Name the query and choose Modify the query design . Choose Finish .

(Step 5) - Place criteria in the query design view. Choose the Run button to see results.

Criteria for Queries


Condition
What it Does
Example
=
Exactly matches =100, =smith
<
Less than <100
<=
Less than or equal to <=100
>
Greater than >100
>=
Greater than or equal to >= 100
<>
Not equal to <>100, <>Texas
Is Null
Empty field  
Between X and Y
Between one word or number and another word or number  
*
Wildcard character

Smi* finds words that begin with Smi such as Smith and Smithsonian

(Step 6) - Place criteria such as an ascending sort in the query and run the query.