Note :

All course guides are in PDF format and you must

have an appropriate PDF reader installed on your
computer to open and print these course guides. If
you do not already have one, you can find and down-
load a free copy of Acrobat Reader at: https://get.
adobe.com/reader/

Course Reference Handout

MICROSOFT ACCESS -
INTERMEDIATE
Contact us at Learn iT!
(415) 693-0250
www.learnit.com

DAY 2
 MICROSOFT ACCESS
Module 03
Access Table Relationships: Establishing Table Relationships:
Relationships between Access tables are required in order to ■■ From the Database Tools tab, click the
allow queries to extract data from multiple tables into a single Relationships command.
result. Tables relate through a common field in both tables. ■■ Drag the respective tables from the
This common field must follow these guidelines: Navigation Panel into the Relationships
■■ Common fields must be the same data-type (i.e., Text or window.
Number). ■■ Size and organize the tables to your
■■ Common fields must be the same field size (i.e., both be preference.
Long Integer, Integer, etc. for Number fields). ■■ Drag the common relatable field from one
■■ Common fields must use the same data in their respective table to the other table’s relatable field.
fields (i.e., use the same Cust ID values for Customers and ■■ In the Edit Relationship dialog box, click
Sales). Create button.
■■ Common field must be the Primary Key in the One-Side of
the Relationship. Access Relationship Window:

Types of Relationships:
There are three types of relationships you may encounter –

Note:
Access can only work with the first two.

One to One:
A One to One relationship exists when for every record in one
table of the relationship there is only one matching record in the
other table of the relationship. This is specific to the field that
the two tables share.
Note:
Example:
The symbols on the relationship lines represent
For each Customer Number in a Customer’s table there is one that Enforce Referential Integrity has been
matching Customer Number in a Credit Info table used to track checked. With this option active, the “Many”-Side
Customer credit card info. The Primary Key would be attached to to the relationship cannot contain any records that
both of these common fields. have a value in the related field that is not already
entered in One-Side of the relationship (i.e., An
One to Many: order with a CustomerID that is not already entered
A One to Many relationships exists when for every record in one into the Customers table). This option creates more
table of the relationship there may be many matching records security and accuracy for data entry and is highly
in the other table of the relationship. This is specific to the field recommended.
that the two tables share.
Example:
For each Customer Number in a Customer’s table there may be
many matching Customer Numbers in an Orders table used to
track orders that Customers place… as in any ONE customer may
place MANY orders. The Primary Key is attached to the field in
One-Side of this relationship.

Many to Many:
A Many to Many relationship exists when two tables that need
to be related cannot be related because no common field to join
the tables exists. This can be solved be creating another table,
known as a Junction Table, which is then used to indirectly join
the two tables in the Many to Many relationship. (This results in
creating two separate One to Many relationships.)

02
 MICROSOFT ACCESS
Module 04
Terms and References:
Query: ■■ To add fields of data to the Query Design Grid in the lower
The Query object of a database is the tool that allows you to pull part of the window, either double-click the fields from the
fields of information from multiple tables of a relational database table field lists above – or – click and drag the fields into the
into a single datasheet. The results of running a query look just grid below. Do this for all fields you want to see in the results
like a table – only the information you see can come from any of your query.
number of individual tables. Queries also allow you to filter out
■■ To see the results of a Select Query either
certain records you do not wish to see, can perform calculations,
click the Datasheet View button or the Run
and perform actions such as automatically updating or deleting
records within a table for you, so you don’t have to. There are button in the Results group of the Ribbon.
several types of queries in Access: Select Queries, Action Queries, ■■ Click the Save button from the Quick
Parameter Queries and Cross Tab Queries to name a few. Access Toolbar – or – use the keyboard
shortcut [CTRL] + [S] to save the query to the
SQL: database.
SQL (Structured Query Language) is essentially the
programming language that a query is written in. Some Access Sorting Query Results:
database designers will work directly in this language when Though the Datasheet view of a query can be sorted the same
creating or modifying a query. If you know the SQL language, way as a table, the Query Design view has an area to include
you can accomplish much more with your queries in Access. multiple field sorting. To sort the records that will appear as the
result of your query before running the query:
Select Query:
■■ Switch to the Design view of your query by clicking
This is the most common of the queries types. Essentially, a
Select Query is simply one in which you have selected various the Design View button on the Ribbon.
fields from different tables of a relational database that you want ■■ Click in the Query Design Grid in the lower part of the
to see together in a single view – called the datasheet view of a window where the Sort Row intersects the field you
query. You can then filter, sort and calculate your results within want to sort by.
the Design View of your select query. ■■ Click the drop-down arrow to the right of the sort
area for the field and select either Ascending or
Descending.
■■ Repeat this for any additional fields you also want to
sort by when using a multiple field sort.
■■ Switch back to the Datasheet view or click the Run
button to see the query’s results.

Filtering Query Results:

Many times, when you run a Query, you will only want to see
certain records as a result.

Tables joined together in the Design View of a Query. Example:

Suppose you want to see a list of employees… but only the
To begin creating a new Select Query in Design
employees who work in the Sales department. A query can be
view:
used to filter only the records you want to see that meet certain
■■ Click the Create tab from the top of the criteria you insert into the query’s Design view.
Ribbon and then click the Query Design
button in the Queries group on the right Criteria:
side of the Ribbon. Criteria are what you use to refine the results of your query to
filter the records you will see as a result. The table to the right list
■■ In the Design view of the query, use the Show Table window
some common operators used to set criteria in a query. To filter
to add any tables (or existing queries) that contain the fields a query’s records using criteria:
of data you want to use in the new query. To add a table,
■■ Switch to Equals =
either doubleclick the table or select the table and click the
the Design Greater Than >
Add button.
view of Greater Than or Equal To >=
■■ Once you have added all the tables containing the fields of
your query
data you want in your query, verify that the table field lists Less Than <
by clicking
in the top part of the query Design view are joined. Every Less Than or Equal To <=
the Design
table in the query needs to have a common field with at Not Equal To <>
View
least one other table in the query, and these fields need Wildcard Character
button on *
to join. If you have used Primary Keys in your tables and/ (unlimited characters)
the Home
or have established Table Relationships, Access will usually Wildcard Character
Ribbon. ?
join the tables for you. (one per character)
Blank or empty data values Is Null

03
 MICROSOFT ACCESS
■■ Click in the Query Design Grid in the lower part of the window where the Criteria Row intersects the field you want to filter by.
■■ Type in the necessary criteria values to filter the data in the field with.
■■ To add more than one criterion to filter by, click in the Or Rows (which includes ALL rows below the Criteria Row – even those that
are not labeled “Or”) and enter the additional criteria as needed.
■■ To apply criteria to more than one field of data at the same time (known as an AND condition) click in the same Criteria Row
containing your other conditions and enter the additional criteria as needed.
■■ View the results of the query by switching to the Design view – or – run the query using the Run button.

Parameter Query: Example:

Parameter Queries can be used to have a query prompt you for
Suppose you have selected a Quantity Sold field and a Unit Price
criteria each time you run the query.
field for your query that you want to multiply together to create
Example: a new field called Subtotal. The calculation would be written as
followed: Subtotal:[Quantity Sold] * [Unit Price]
If you created a query that filtered employee records by the “Sales”
department (by using the criteria = “sales” in the Criteria Row of
a Department Name field) the query would filter by the same When typing in a confined area, like the
department each time you opened it. You would conceivably Field Name box in the Query Design Grid, try
have to create and save a separate query for each department of pressing [SHIFT] + [F2] to Zoom in on the area.
employees you want to filter by. With a Parameter Query, each
time you run the query you will be prompted to enter the criteria
you want to filter the records by… and therefore each time you Create a Calculated Field in a Query:
run the query, you could enter different criteria values to get ■■ To create the calculated field using the Expression Builder:
different results – all from a single query! click the Builder button from the Query Toolbar
To create a Parameter Query that will prompt for criteria: and select fields displayed in the Expression Builder
window that you want to use in the calculation, inserting
■■ Create a Select query with the data fields you want to
mathematical operators where needed. Click the OK button
appear in your resulting records.
when finished. In the top row of the column, you created
■■ Click in the Criteria Row where it intersects the column
the calculation in, replace the generic reference of expr1
containing the data field you want to filter by.
with the actual field’s name.
■■ In the criteria row area, type in a message prompt within
a set of square brackets to create the parameter prompt. Note:
For example – to create a prompt for a department name
Save the query first to allow the Expression Builder
criteria, you would type the following into the Criteria Row
to be more help in selecting the fields for the
of the Region field. [Enter the region you want to view:]
calculation.
■■ Run the query
or switch to ■■ View the query
the query’s Datasheet or run
Datasheet the query to see
view to the results of the
activate the calculation.
parameter
prompt.

Performing Calculations with a Query:

When you need to have calculations performed using data
within your database, a query is one object of the database
that can perform these calculations for you. When creating a
calculative field in a query, you can either write the calculation Action Queries:
manually, or you can get assistance from Access by using the An Action Query is a query that does more than just display the
Expression Builder. records containing the fields of data that you have selected.
Instead, Action Queries actually perform some type of action –
■■ Begin a new query in Design view and add any fields of data
depending on the Action Query you have used – on the resulting
you need in your query’s results into the Query Design Grid.
records. You can also use criteria in an Action Query to specify
■■ Click in the top row (Field Name box) of the next available, certain records you want the action to focus on. Following is a list
empty column that will be used to display the results of of the four action queries and the action that is executed with
your calculation. the resulting records:
■■ To create the calculated field manually: type in the field
■■ Make Table Query: The fields and records from the query
name, followed by a colon “:”, followed by any names of
are made into a new Table within the database.
fields surrounded by “square brackets” you want referenced
■■ Append Query: The fields and records from the query are
in the calculation. Insert mathematical operators where
inserted into, or appended to, an existing Table within the
needed.
database.
■■ Update Query: Allows you to select fields of data from an
existing table that you want to update or modify.
■■ Delete Query: The fields and records from the query are
deleted from an existing Table within the database.

04
 MICROSOFT ACCESS
Using an Action Query: Importing Data:
To switch from a normal Select Query to one of the Action Data from other files, such as Excel worksheets or delimited text
Queries: files, can be imported into an Access database as a table. You
■■ Begin a new query in Design view. can also import objects from other Access databases such as
existing tables, queries, forms and reports.
■■ Add the tables that contain the fields of data you need for
your Action Query. Importing Objects from another Database:
■■ Select only the fields of data from the table or tables that To import a table, query, form, report or other objects from
you want effected by the specific action of the Action Query another database:
you will be using. ■■ Click on the External Data command tab from top of the
■■ To select one of the Action queries, click on appropriate Ribbon.
button from the Query Type group in the Ribbon. ■■ Select one of the buttons from the Import group of the
■■ RUN the Action query to execute the action by clicking the Ribbon according to the type of data you want to import.
Run button. ■■ In the Import Objects window, select any objects from
the tabs provided that you want to import. Click OK when
finished.

Note:
Note:
You can also link data from a table of one database
Clicking the Datasheet view button will NOT to a table in another database. Instead of selecting
execute the action of an Action Query. You must the Import Tables… from the Get External Data
click the actual Run button instead for an Action window, select Link Tables… and then choose the
Query to execute. database file and tables you want to import with a
link established.

Advanced Report Design: Types of Form/Report Controls:

Reports are typically a “graphic” view of data in underlying Bound ...........................................Actual field in the underlying table
tables. Reports can be based on either Tables or Queries. Users
can’t perform data entry (add, edit, delete records) in a report. Unbound ..........................Title/Rectangle/Line/Picture/Logo
This database object can only display the data in a more
Calculated ......Used for Calculations, Uses Bound Controls
“professional” way.
There are 3 main types of form design layouts:
LABEL TEXT BOX
■■ Tabular
■■ Columnar
■■ Mailing Labels
"Un-Hinging" Report Controls:
There are 4 views from which to display or customize a report:
By default, Access groups all the controls in reports so that if one
is moved or sized all the controls are affected at the same time.
To break up the controls into individually controlled objects do
the following:

05
 MICROSOFT ACCESS
Steps:
■■ View the report in Design View or Layout View
■■ Click on a control (a field) in the Detail section.
■■ Find and click the small somewhat hidden icon that is
represented as a 4-headed arrow inside a box.
■■ On the Arrange tab, click the Remove button.
■■ Click away from all the controls.
■■ Now you can individually size and move any control.

Grouping and Summarizing Reports –

Groups:
Using the Group & Sort panel, you can organize and summarize
your records by one or more fields.

06