Microsoft Access Tutorial
Overview
MS Access is frequently used by small companies to create simple database solutions. Using Access, you don’t have to know too much about databases and will still be able to easily construct simple, aesthetically pleasing database solutions. This file has hyperlinks to some reference material. The bulk of the material that follows was copied from the following website: http://www.fgcu.edu/support/office2000/access
Part 1: Tables
A database is the container for the data and associated objects. Objects include tables, queries, forms, reports, macros, and modules. In Access, the “table” holds the actual data, so begin your review with basic table operations. If required, you can review the following before starting (headings are linked to an online Tutorial).
- A database is a collection of related
information.
- 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.
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.
For this class you should create
tables using the Design view only!! 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 Properties Properties for each field are
set from the bottom pane of the Design View window.
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. Indexes 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 allow only all data strings three characters in length. Input Masks 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 "(555)
123-4567". The blank field would look like (___) ___-____. An an input mask to a field by following these steps:
Adding Records 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 record.
Editing Records 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. Deleting Records 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. Freezing Columns 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.
Hiding 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
button.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
![[Database Window]](AccessTutorial_files/image001.gif)
![[Design View]](AccessTutorial_files/image002.gif)
![[Datasheet View]](AccessTutorial_files/image003.gif)
![[Freeze Columns example]](AccessTutorial_files/image004.gif)
![[Unhide Columns window]](AccessTutorial_files/image005.gif)
Part 2: Forms
Create Form in Design View
- 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.
![[toolbox diagram]](AccessTutorial_files/image006.gif)
- 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 the form.
![[form example]](AccessTutorial_files/image007.gif)
- To add command buttons to your form, select the
“Command Button” icon from the toolbox, and then click on your form. From
the Command Wizard window that shows up, select the action you want
associated with your button: Go To Next Record
(Record Navigation category), Add New Record (from Record Operations
category), Delete Record (Record Operations category), Quit Application
(Application category), Run Query (Miscellaneous category), etc.
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, or the appropriate Add Record button, If one was created for the form. Records are automatically
saved as they are entered so no additional manual saving needs to be executed.
![[Form View]](AccessTutorial_files/image009.gif)
Editing Forms
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.
![[Tab Order dialog box]](AccessTutorial_files/image011.jpg)
- 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:
![[formatting toolbar]](AccessTutorial_files/image012.gif)
- 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.
Part 3: 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 button.
![[New Query]](AccessTutorial_files/image014.jpg)
- 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 selected.
![[Show Table window]](AccessTutorial_files/image016.jpg)
- 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.
![[Select Query window]](AccessTutorial_files/image018.jpg)
- 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
![[Expression Builder button]](AccessTutorial_files/image019.gif)
can also be used to assist in writing
the expressions.
|
Query Wildcards and Expression
Operators |
|
|
Wildcard / Operator |
Explanation |
|
?
Street |
The
question mark is a wildcard that takes the place of a single letter. |
|
43th * |
The
asterisk is the wildcard that represents a number of characters. |
|
<100 |
Value
less than 100 |
|
>=1 |
Value
greater than or equal to 1 |
|
<>"FL" |
Not
equal to (all states besides |
|
Between
1 and 10 |
Numbers
between 1 and 10 |
|
Is
Null |
Finds
records with no value |
|
Like
"a*" |
All
words beginning with "a" |
|
>0
And <=10 |
All numbers
greater than 0 and less than 10 |
|
"Bob"
Or "Jane" |
Values
are Bob or Jane |
- 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.
Logical OR vs. Logical
Part 4: Reports
Create in Design
View
To create a report from scratch,
select Design View from the Reports Database Window.
- Click the New button on the Reports
Database Window. Highlight "Design View" and choose the data
source of the report from the drop-down menu and click OK.
![[New Report window]](AccessTutorial_files/image020.gif)
- You will be presented with a blank grid with a
Field Box and form element toolbar that looks similar to the Design View
for forms. Design the report in much the same way you would create a form.
For example, double-click the title bar of the Field Box to add all of the
fields to the report at once. Then, use the handles on the elements to
resize them, move them to different locations, and modify the look of the
report by using options on the formatting toolbar. Click the Print View
button at the top, left corner of the screen to preview the report.
![[form edit window]](AccessTutorial_files/image021.gif)
Printing Reports
Select File|Page
Setup to modify the page margins, size, orientation, and column setup.
After all changes have been made, print the report by selecting File|Print from the menu bar or click the Print
button on the toolbar.