M. S. Office:Access
What we have for you in this
These words are used often in Access so you will want to become familiar
with them before using the program and this tutorial.
- A database is a collection
of related information.
- An object is a competition
in the database such as a table, query, form, or macro.
- A table is a grouping of
related data organized in fields (columns) and records (rows) on a
datasheet. By using a common field in two tables, the data can be
combined. Many tables can be stored in a single database.
- A field is a column on a
datasheet and defines a data type for a set of values in a table. For a
mailing list table might include fields for first name, last name,
address, city, state, zip code, and telephone number.
- A record in a row on a
datasheet and is a set of values defined by fields. In a mailing list
table, each record would contain the data for one person as specified by
the intersecting fields.
- Design View provides the
tools for creating fields in a table.
- Datasheet View allows you
to update, edit, and delete in formation from a table.
After opening Access, you will be presented with the window shown below.
Select one of the first two options if you are creating a new database, or
the third if you want to edit an existing database. All three choices are
explained in detail below.
Blank Access database
- Unlike Word documents, Excel worksheets, and Power Point
presentations, you must save an Access database before you start working
on it. After selecting "Blank Access database", you will first be
prompted to specify a location and name for the database.
- Find the folder where the database should reside in the Save in
- Type the name of the database in the File name line and click the Create button.
Access database wizards, pages, and projects
Access' wizards and layout are existing database structures that
only need data input. Select a database type and click OK. Name the
database on the next screen.
Open an existing database
If the database was opened recently on the computer, it will be listed
on the main window. Highlight the database name and click OK.
Otherwise, highlight "More Files..." in the list and click OK. From the subsequent window, click the "Look In:" drop-down
menu to find the folder where the database is located, highlight the
database name in the listing and click OK.
Converting to Access 2000
Before opening an existing file that was created in a previous version
of Access, it must first be converted to Access 2000 format. Convert a
database by following these steps:
- Open Access and select Tools|Database Utilities|Convert Database|To Current Access
Database Version from the menu bar.
- Select the database that should be converted and click the
- The new version will be a completely separate database and the old
one will remain intact so you must then name the new version of the
The Database Window organizes all of the objects in the database. The
default tables listing provides links for creating tables and will list
all of the tables in the database when they have been added.
Design View customizes the fields in the database so that data can be
The datasheet allows you to enter data into the database
Introduction to Tables
Tables are grids that store information in a database similar to the way
an Excel worksheet stores information in a workbook. Access provides three
ways to create a table for which there are icons in the Database Window.
Double-click on the icons to create a table.
the Database Window
- Create table in Design view will allow you to create the
fields of the table. This is the most common way of creating a table and
is explained in detail below.
- Create table using wizard will step you through the creation
of a table.
- Create table by entering data will give you a blank datasheet
with unlabelled columns that looks much like an Excel worksheet. Enter
data into the cells and click the Save button. You will be
prompted to add a primary key field. After
the table is saved, the empty cells of the datasheet are trimmed. The
fields are given generic names such as "Field1", "Field2", etc. To
rename them with more descriptive titles that reflect the content of the
fields, select Format|Rename Column from the menu bar or highlight the
column, right-click on it with the mouse, and select Rename Column
from the shortcut menu.
Create a Table in Design View
Design View will allow you to define the fields in the table before
adding any data to the datasheet. The window is divided into two parts: a
top pane for entering the field name, data type, and an option description
of the field, and a bottom pane for specifying field properties.
- Field Name - This is the name of the field and should
represent the contents of the field such as "Name", "Address", "Final
Grade", etc. The name can not exceed 64 characters in length and may
- Data Type is the type of value that will be entered into the
- Text - The default type, text type allows any
combination of letters and numbers up to a maximum of 255
characters per field record.
- Memo - A text type that stores up to 64,000 characters.
- Number - Any number can be stored.
- Date/Time - A date, time, or combination of both.
- Currency - Monetary values that can be set up to
automatically include a dollar sign ($) and correct decimal
and comma positions.
- AutoNumber - When a new record is created, Access will
automatically assign a unique integer to the record in this
field. From the General options, select Increment if the
numbers should be assigned in order or random if any random
number should be chosen. Since every record in a datasheet
must include at least one field that distinguishes it from
all others, this is a useful data type to use if the
existing data will not produce such values.
- Yes/No - Use this option for True/False, Yes/No,
On/Off, or other values that must be only one of two.
- OLE Object - An OLE (Object Linking and Embedding)
object is a sound, picture, or other object such as a Word
document or Excel spreadsheet that is created in another
program. Use this data type to embed an OLE object or link
to the object in the database.
- Hyperlink - A hyperlink will link to an Internet or
Intranet site, or another location in the database. The data
consists of up to four parts each separated by the pound
sign (#): DisplayText#Address#SubAddress#ScreenTip. The
Address is the only required part of the string. Examples:
Description (optional) - Enter a brief description of what
the contents of the field are.
Field Properties - Select any pertinent properties for the
field from the bottom pane.
|Internet hyperlink example:
||FGCU Home Page#http://www.fgcu.edu#
|Database link example:
Properties for each field are set from the bottom pane of the Design
- Field Size is used to set the number of characters needed in
a text or number field. The default field size for the text type is 50
characters. If the records in the field will only have two or three
characters, you can change the size of the field to save disk space or
prevent entry errors by limiting the number of characters allowed.
Likewise, if the field will require more than 50 characters, enter a
number up to 255. The field size is set in exact characters for Text
type, but options are give for numbers:
- Byte - Positive integers between 1 and 255
- Integer - Positive and negative integers between
-32,768 and 32,768
- Long Integer (default) - Larger positive and negative
integers between -2 billion and 2 billion.
- Single - Single-precision floating-point number
- Double - Double-precision floating-point number
- Decimal - Allows for Precision and Scale property
- Format conforms the data in the field to the same format when
it is entered into the datasheet. For text and memo fields, this
property has two parts that are separated by a semicolon. The first part
of the property is used to apply to the field and the second applies to
Text and memo format.
@ indicates a required
character or space
& indicates an optional
character or space
< converts characters to lowercase
> converts characters to uppercase
\ adds characters to the end
|@;"No Data Entered"
|@;"No Data Entered"
No Data Entered
Number format. Select one of the preset options from
the drop down menu or construct a custom format using symbols explained
||0 is a placeholder that displays a digit or 0 if
there is none.
# is a placeholder that displays a
digit or nothing if there is none.
% multiplies the number by 100 and added a percent
Currency format. This formatting consists of four parts
separated by semicolons:
format for positive numbers; format for negative numbers;
format for zero values; format for Null values.
Positive values will be normal currency format,
negative numbers will be red in parentheses, zero is
entered for zero values, and "none" will be written
for Null values.
Date format. In the table below, the value "1/1/01" is
entered into the datasheet, and the following values are displayed as a
result of the different assigned formats.
Monday, January 1, 2001
dddd, mmmm, and yyyy print the full day name, month
name, and year
|ddd","mmm ". " d", '"yy
Mon, Jan. 1, '01
ddd, mmm, and yy print the first three day letters,
first three month letters, and last two year digits
|"Today is " dddd
Today is Monday
"n" is used for minutes to
avoid confusion with months
Yes/No fields are displayed as check boxes by default
on the datasheet. To change the formatting of these fields, first click
the Lookup tab and change the Display Control to a text box. Go back to
the General tab choices to make formatting changes. The formatting is
designated in three sections separated by semicolons. The first section
does not contain anything but the semicolon must be included. The second
section specifies formatting for Yes values and the third for No values.
Prints "Yes" in green or "No" in red
Default Value - There may be cases where the value of a field
will usually be the same for all records. In this case, a changeable
default value can be set to prevent typing the same thing numerous
times. Set the Default Value property.
Every record in a table must have a primary key that differentiates it
from every other record in the table. In some cases, it is only necessary
to designate an existing field as the primary key if you are certain that
every record in the table will have a different value for that particular
field. A social security number is an example of a record whose values
will only appear once in a database table.
Designate the primary key field by right-clicking on the record and
selection Primary Key from the shortcut menu or select Edit|Primary
Key from the menu bar. The primary key field will be noted with a key
image to the left. To remove a primary key, repeat one of these steps.
If none of the existing fields in the table will produce unique values
for every record, a separate field must be added. Access will prompt you
to create this type of field at the beginning of the table the first time
you save the table and a primary key field has not been assigned. The
field is named "ID" and the data type is "autonumber". Since this extra
field serves no purpose to you as the user, the autonumber type
automatically updates whenever a record is added so there is no extra work
on your part. You may also choose to hide this column in the datasheet as
explained on a later page in this tutorial.
Creating indexes allows Access to query and sort records faster. To set
an indexed field, select a field that is commonly searched and change the
Indexed property to Yes (Duplicates OK) if multiple entries of the same data value
are allowed or Yes (No Duplicates) to prevent duplicates.
Field Validation Rules
Validation Rules specify requirements (change word) for the data entered
in the worksheet. A customized message can be displayed to the user when
data that violates the rule setting is entered. Click the expression
builder ("...") button at the end of the Validation Rule box to write the
validation rule. Examples of field validation rules include <> 0 to
not allow zero values in the record, and ??? to only all data
strings three characters in length.
An input mask controls the value of a record and sets it in a specific
format. They are similar to the Format property, but instead display the
format on the datasheet before the data is entered. For example, a
telephone number field can formatted with an input mask to accept ten
digits that are automatically formatted as "(999) 888-7777". The blank
field would look like (___) ___-____. An an input mask to a field by
following these steps:
(@@@) @@@-@@@@, into the field provided. The
following symbols can be used to create an input mask from scratch:
- In design view, place the cursor in the field that the input mask
will be applied to.
- Click in the white space following Input Mask under the General tab.
- Click the "..." button to use the wizard or enter the mask,
|Input Mask Symbols
Letter or digit
A digit 0 through 9 without a + or - sign and with
blanks displayed as zeros
Same as 0 with blanks displayed as spaces
Same as 9 with +/- signs
Letter A through Z
|C or &
Character or space
Convert letters to lower case
Convert letters to upper case
Add new records to the table in datasheet view by typing in the record
beside the asterisk (*) that marks the new record. You can also click the
new record button at the bottom of the datasheet to skip to the last empty
To edit records, simply place the cursor in the record that is to be
edited and make the necessary changes. Use the arrow keys to move through
the record grid. The previous, next, first, and last record buttons at the
bottom of the datasheet are helpful in maneuvering through the datasheet.
Delete a record on a datasheet by placing the cursor in any field of the
record row and select Edit|Delete Record from the menu bar or click the Delete
Record button on the datasheet toolbar.
Adding and Deleting Columns
Although it is best to add new fields (displayed as columns in the
datasheet) in design view because more options are available, they can
also be quickly added in datasheet view. Highlight the column that the new
column should appear to the left of by clicking its label at the top of
the datasheet and select Insert|Column from the menu bar.
Entire columns can be deleted by placing the cursor in the column and
selecting Edit|Delete Column from the menu bar.
Resizing Rows and Columns
The height of rows on a datasheet can be changed by dragging the gray
sizing line between row labels up and down with the mouse. By changing the
height on one row, the height of all rows in the datasheet will be changed
to the new value.
Column width can be changed in a similar way by dragging the sizing
line between columns. Double click on the line to have the column
automatically fit to the longest value of the column. Unlike rows, columns
on a datasheet can be different widths. More exact values can be assigned
by selecting Format|Row Height or Format|Column Width from the menu bar.
Similar to freezing panes in Excel, columns on an Access table can be
frozen. This is helpful if the datasheet has many columns and relevant
data would otherwise not appear on the screen at the same time. Freeze a
column by placing the cursor in any record in the column and select Format|Freeze Columns from the menu bar. Select the same option
to unfreeze a single column or select Format|Unfreeze All Columns.
Columns can also be hidden from view on the datasheet although they will
not be deleted from the database. To hide a column, place the cursor in
any record in the column or highlight multiple adjacent columns by
clicking and dragging the mouse along the column headers, and select Format|Hide Columns from the menu bar.
To show columns that have been hidden, select Format|Unhide Columns
from the menu bar. A window displaying all of the fields in the table will
be listed with check boxes beside each field name. Check the boxes beside
all fields that should be visible on the data table and click the Close
Finding Data in a Table
Data in a datasheet can be quickly located by using the Find
- Open the table in datasheet view.
- Place the cursor in any record in the field that you want to search
and select Edit|Find... from the menu bar.
- Enter the value criteria in the Find What: box.
- From the Look In: drop-down menu, define the area of the
search by selecting the entire table or just the field in the table you
placed your cursor in during step 2.
- Select the matching criteria from Match: to and click the More >> button for additional search parameters.
- When all of the search criteria is set, click the Find Next
button. If more than one record meets the criteria, keep clicking Find Next until you reach the correct record.
The replace function allows you to quickly replace a single occurrence
of data with a new value or to replace all occurrences in the entire
- Select Edit|Replace... from the menu bar (or click the Replace
tab if the Find window is already open).
- Follow the steps described in the Find procedure for searching for
the data that should be replaced and type the new value of the data in
the Replace With: box.
- Click the Find Next button to step through occurrences of the
data in the table and click the Replace button to make single
replacements. Click Replace All to change all occurrences of the
data in one step.
Check Spelling and AutoCorrect
The spell checker can be used to flag spelling errors in text and menu
fields in a datasheet. Select Tools|Spelling from the menu bar to activate the spell checker
and make corrections just as you would using Word or Excel. The
AutoCorrect feature can automatically correct common spelling errors such
as two INitial CApitals, capitalizing the first letter of the first word
of a sentence, and anything you define. Select Tools|AutoCorrect to set these features.
Print a Datasheet
Datasheets can be printed by clicking the Print button on the toolbar or select File|Print to set more printing options.
To prevent the duplication of information in a database by repeating
fields in more than one table, table relationships can be established to
link fields of tables together. Follow the steps below to set up a
- Click the Relationships button on the toolbar.
- From the Show Table window (click the Show Table
button on the toolbar to make it appear), double click on the names of
the tables you would like to include in the relationships. When you have
finished adding tables, click Close.
- To link fields in two different tables, click and drag a field from
one table to the corresponding field on the other table and release the
mouse button. The Edit Relationships window will appear. From this window,
select different fields if necessary and select an option from Enforce
Referential Integrity if necessary. These options give Access permission
to automatically make changes to referential tables if key records in
one of the tales is deleted. Check the Enforce Referential Integrity
box to ensure that the relationships are valid and that the data is not
accidentally deleted when data is added, edited, or deleted. Click
Create to create the link.
- A line now connects the two fields in the Relationships window.
- The datasheet of a relational table will provide expand and collapse
indicators to view subdatasheets containing matching information from
the other table. In the example below, the student address database and
student grade database were related and the two can be shown
simultaneously using the expand feature. To expand or collapse all
subdatasheets at once, select Format|Subdatasheet|Expand All or
Collapse All from the toolbar.
Sorting and Filtering
Sorting and filtering allow you to view records in a table in a
different way either by reordering all of the records in the table or view
only those records in a table that meet certain criteria that you specify.
You may want to view the records in a table in a different order than they
appear such as sorting by a date or in alphabetical order, for example.
Follow these steps to execute a simple sort of records in a table based on
the values of one field:
- In table view, place the cursor in the column that you want to sort
- Select Records|Sort|Sort Ascending or Records|Sort|Sort Descending from the menu bar or click the
Sort Ascending or Sort Descending buttons on the toolbar.
To sort by more than one column (such as sorting by date and then
sorting records with the same date alphabetically), highlight the columns
by clicking and dragging the mouse over the field labels and select one of
the sort methods stated above.
Filter by Selection
This feature will filter records that contain identical data values in a
given field such as filtering out all of the records that have the value
"Smith" in a name field. To Filter by Selection, place the cursor in the
field that you want to filter the other records by and click the Filter by Selection button on the toolbar or select
Records|Filter|Filter By Selection from the menu bar. In the example
below, the cursor is placed in the City field of the second record that
displays the value "Ft. Myers" so the filtered table will show only the
records where the city is Ft. Myers.
Filter by Form
If the table is large, it may be difficult to find the record that
contains the value you would like to filter by so using Filter by Form may
be advantageous instead. This method creates a blank version of the table
with drop-down menus for each field that each contain the values found in
the records of that field. Under the default Look for tab of the
Filter by Form window, click in the field to enter the filter criteria. To
specify an alternate criteria if records may contain one of two specified
values, click the Or tab at the bottom of the window and select another criteria
from the drop-down menu. More Or tabs will appear after one criteria is set to allow you to
add more alternate criteria for the filter. After you have selected all of
the criteria you want to filter, click the Apply Filter button on
The following methods can be used to select records based on the record
selected by that do not have exactly the same value. Type these formats
into the field where the drop-down menu appears instead of selecting an
Filter by Form
||Selects all records that end
||Selects all records that
begin with the letters A through G
||Selects all dates since
||Selects all records not equal
Saving A Filter
The filtered contents of a table can be saved as a query by selecting
File|Save As Query from the menu bar. Enter a name for the query and
click OK. The query is now saved within the database.
Remove a Filter
To view all records in a table again, click the depressed Apply Filter
toggle button on the toolbar.
Introduction to Queries
Queries select records from one or more tables in a database so they can
be viewed, analyzed, and sorted on a common datasheet. The resulting
collection of records, called a dynaset (short for dynamic subset), is saved as a database
object and can therefore be easily used in the future. The query will be
updated whenever the original tables are updated. Types of queries are select queries that extract data from tables based on
specified values, find duplicate queries that display records with duplicate
values for one or more of the specified fields, and find unmatched
queries display records from one table that do not have corresponding
values in a second table.
Create a Query in Design View
Follow these steps to create a new query in Design View:
- From the Queries page on the Database Window, click the New
- Select Design View and click OK.
- Select tables and existing queries from the Tables and Queries tabs and click the Add button to add each one to the new query.
- Click Close when all of the tables and queries have been
- Add fields from the tables to the new query by
double-clicking the field name in the table boxes or selecting
the field from the Field: and Table: drop-down
menus on the query form. Specify sort orders if necessary.
- Enter the criteria for the query in the Criteria:
field. The following table provides examples for some of the
wildcard symbols and arithmetic operators that may be used. The
Expression Builder can also be used to assist in writing the
|Query Wildcards and Expression Operators
|Wildcard / Operator
||The question mark
is a wildcard that takes the place of a single
||The asterisk is
the wildcard that represents a number of characters.
||Value less than
than or equal to 1
||Not equal to (all
states besides Florida)
|Between 1 and 10
||Numbers between 1
Is Not Null
with no value
or all records that have a value
beginning with "a"
|>0 And <=10
greater than 0 and less than 10
|"Bob" Or "Jane"
||Values are Bob or
After you have selected all of the fields and tables, click
the Run button on the toolbar.
Save the query by clicking the Save button.
Access' Query Wizard will easily assist you to begin creating a select
- Click the Create query by using wizard icon in the database
window to have Access step you through the process of creating a query.
- From the first window, select fields that will be included in the
query by first selecting the table from the drop-down Tables/Queries
menu. Select the fields by clicking the > button to move the
field from the Available Fields list to Selected Fields. Click the
double arrow button >> to move all of the fields to Selected Fields. Select
another table or query to choose from more fields and repeat the process
of moving them to the Selected Fields box. Click Next > when all
of the fields have been selected.
- On the next window, enter the name for the query and click Finish.
- Refer to steps 5-8 of the previous tutorial to add more parameters
to the query.
Find Duplicates Query
This query will filter out records in a single table that contain
duplicate values in a field.
- Click the New button on the Queries database window, select Find Duplicates Query Wizard from the New Query window and click OK.
- Select the table or query that the find duplicates query will be
applied to from the list provided and click Next >.
- Select the fields that may contain duplicate values by highlighting
the names in the Available fields list and clicking the > button
to individually move the fields to the Duplicate-value fields list or
>> to move all of the fields. Click Next > when all fields
have been selected.
- Select the fields that should appear in the new query along with the
fields selected on the previous screen and click Next >.
- Name the new query and click Finish.
Delete a Query
To delete a table from the query, click the table's title bar and press
the Delete key on the keyboard.
Forms are used as an alternative way to enter data into a database
Create Form by Using Wizard
To create a form using the assistance of the wizard, follow these steps:
- Click the Create form by using wizard option on the database
- From the Tables/Queries drop-down menu, select the table or query
whose datasheet the form will modify. Then, select the fields that will
be included on the form by highlighting each one the Available Fields
window and clicking the single right arrow button > to move the field to the Selected Fields window. To move all of the fields to Select
Fields, click the double right arrow button >>. If you make a
mistake and would like to remove a field or all of the fields from the
Selected Fields window, click the left arrow < or left double arrow << buttons. After the proper fields have been selected, click
the Next > button to move on to the next screen.
- On the second screen, select the layout of the form.
Click the Next > button to move on to the next screen.
- Columnar - A single record is displayed at one
time with labels and form fields listed side-by-side in
- Justified - A single record is displayed with
labels and form fields are listed across the screen
- Tabular - Multiple records are listed on the page
at a time with fields in columns and records in rows
- Datasheet - Multiple records are displayed in
- Select a visual style for the form from the next set of
options and click Next >.
- On the final screen, name the form in the space provided.
Select "Open the form to view or enter information" to open the
form in Form View or "Modify the form's design" to open it in
Design View. Click Finish to create the form.
Create Form in Design View
To create a form from scratch without the wizard, follow these steps:
- Click the New button on the form database window.
- Select "Design View" and choose the table or query the form will be
associated with the form from the drop-down menu.
- Select View|Toolbox from the menu bar to view the floating toolbar
with additional options.
Add controls to the form by clicking and dragging the field names
from the Field List floating window. Access creates a text box for the
value and label for the field name when this action is accomplished. To
add controls for all of the fields in the Field List, double-click the
Field List window's title bar and drag all of the highlighted fields to
Adding Records Using A Form
Input data into the table by filling out the fields of the form. Press
the Tab key to move from field to field and create a new record by
clicking Tab after the last field of the last record. A new record can
also be created at any time by clicking the New Record button
at the bottom of the form window. Records are automatically saved as they
are entered so no additional manual saving needs to be executed.
The follow points may be helpful when modifying forms in Design View.
- Grid lines - By default, a series of lines and dots
underlay the form in Design View so form elements can be easily aligned.
To toggle this feature on and off select View|Grid from the menu bar.
- Snap to Grid - Select Format|Snap to Grid to align form objects with the grid to
allow easy alignment of form objects or uncheck this feature to allow
objects to float freely between the grid lines and dots.
- Resizing Objects - Form objects can be resized by
clicking and dragging the handles on the edges and corners of the
element with the mouse.
- Change form object type - To easily change the type of
form object without having to create a new one, right click on the
object with the mouse and select Change To and select an available object type from the list.
- Label/object alignment - Each form object and its
corresponding label are bounded and will move together when either one
is moved with the mouse. However, to change the position of the object
and label in relation to each other (to move the label closer to a text
box, for example), click and drag the large handle at the top, left
corner of the object or label.
- Tab order - Alter the tab order of the objects on the
form by selecting View|Tab Order... from the menu bar. Click the gray box
before the row you would like to change in the tab order, drag it to a
new location, and release the mouse button.
- Form Appearance - Change the background color of the form
by clicking the Fill/Back Color button on the formatting toolbar and click
one of the color swatches on the palette. Change the color of
individual form objects by highlighting one and selecting a
color from the Font/Fore Color palette on the formatting
toolbar. The font and size, font effect, font alignment, border
around each object, the border width, and a special effect can
also be modified using the formatting toolbar:
- Page Header and Footer - Headers and footers added to a
form will only appear when it is printed. Access these sections
by selecting View|Page Header/Footer on the menu bar. Page numbers can
also be added to these sections by selecting Insert|Page Numbers. A date and time can be added from
Insert|Date and Time.... Select View|Page Header/Footer
again to hide these sections from view in Design View.