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 and modified from the following websites: http://www.fgcu.edu/support/office2000/access and http://office.microsoft.com/en-us/access-help/CH010372755.aspx
Part 0: Databases
A database is a collection of related information. The database is the container for the data and associated objects. Objects include tables, queries, forms, reports, macros, and modules.
To create a new database in Access 2010, start MS Access 2010, and select New->Blank Database. Fill out the filename for your database (yourlastname_labX.accdb for this class) and, if needed, change the directory where the database is saved.
Part 1: Tables
In Access, the “table” holds the actual data, so we will begin your review with basic table operations. Below are a few useful definitions.
- 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.
- The primary key is one fields or a combination of fields that
holds only unique values and can identify a particular record. For
example, in a Midshipman table, the "alpha" can be declared as
the primary key.
- Design View provides the
tools for creating fields in a table.
- Datasheet View allows you to
update, edit, and delete information(records) 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 several ways to create a
table. The default one is by using Datasheet View (see below), but we will
use the Design View.
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
a Table in Design View Design view allows you to build a table from scratch and set or change every available property for each field. You can also open existing tables in Design view and add, remove, or change fields.
See below for more details: 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.
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. Validation
Rule Validation Rules
specify requirements for the data entered in the worksheet. Examples of field
validation rules include: <> 0 to not allow zero values, >=
0 to only allow numbers that are greater or equal to 0 ="A" or
= "B" to only allow value A or value B ??? to allow only all data strings three
characters in length, etc. You can click the
expression builder ("...") button at the end of the
Validation Rule box to write the validation rule. Validation
Text A customized
message can be displayed to the user when data that violates the rule setting
is entered, so the user knows how to correct the errors. 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:
Primary
Key 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 (or records) and selecting 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. 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. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
On
the View group in the navigation bar, click Design View. You
will be prompted to give a name to the table (give a meaningful name) and
save it.
In
the Field Name column of the designer, enter the names of your table
fields. By default, each new table has a first field called ID that is the
primary key for the table. You can delete that field if you want to create
your own primary key (right-click and select Delete Rows from the menu) . 
In
the Data Type column, use the list next to a field name to choose a
data type for that field.
Optionally,
use the Field Properties pane to set properties for individual fields.
![[Datasheet View]](AccessTutorial_files/image018.jpg)
Part 2: Forms
Forms allow you to control the look and feel of the screen for the input of data and the reports generated.
Form Views
There are three ways to view forms in Access:
|
|
Allows you to design a form that includes a header, a footer, and details in the form. You can also add images and control which fields appear on the form. |
|
Form View |
This is a dynamic page which allows the user to enter and edit data or navigate through data in a field. |
|
Layout View |
This view allows you to design the form and manipulate data. |
Create a Form
You can create a form from a table or a query. To create a form:
- Click the Create tab
- Option 1: Click the Form button - this will create a form with all the fields in the table already in the form.
- Option 2: Click the Form Design button to create a blank form
- Click the Format tab
- Click the Add Existing Fields Button
- In the Field List box on the right, click and drag the fields you would like on the form
- To add command buttons to your form, while in Design View, select
the “Button” icon from the "Controls" group, 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.
To preview the form:
- Click the Views button on the Home tab
- Click the Form View button
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/image030.jpg)
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/image032.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/image034.jpg)
- 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.
To design a query using the
Query Design Button:
- Click the Query Design Button
on the Create tab
- Select the tables that you
would like to query
- Click Add
- Double click the name of the
field you would like to query
- Repeat this process for as many
fields as you would like in the query
Query Criteria
Query criteria are search conditions used in a query to retrieve specific
data. You can set query criteria to be a specific number or data set, or
you can set the criteria to be a range of data.
To specify search
criteria:
- Click the query that you wish
to add conditions
- Type in the appropriate query
criteria in the Criteria Box for the field that has constraints. For example, if the ID needs to
be "Smith", you would have the example below:
- 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/image042.jpg)
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 |
Note:
Logical OR vs. Logical
- Click Run if you want to
execute the query and see the results:
- Save the query by clicking the Save
button.
Part 4: Reports
Reports are a means to view and analyze large amounts of data from underlying tables or queries. You can use the Report Wizard or create a custom report that meets your specific needs.
Report
Views
Reports can be displayed in four views:
|
|
This view provides you with the structure of your report. You can add, modify or delete components of the report but you cannot manipulate the data in the tables associated with the report. |
|
Report View |
This view allows you to view the data from the table but not to change any layout of the report. |
|
Layout View |
This view allows you to see data from the table and add, modify, and delete components of the report. |
|
Print Preview |
This view allows you to see what your report will look like when it is printed. |
To change report views:
- Click the View button on the Home tab
Create
a Report;
To create a basic report based on
the data in the current query or table:
- While the Table or the Query on which you want to base your report is opened and the tab is active in Access, click the Report button on the Create tab
- Change to Design View to customize your report if needed
- If you want to change the source of the date displayed in the report, change to Design View, select “Property Sheet” from the toolbar and modify the “Record Source” for the “Report” object to be the table or query desired.
To create a blank report:
- Click the Blank Report button on the Create tab
- Click the Add Existing Fields button
- From the field list, Click and drag the fields to the report
