Access

1
Access
Part II
2
More on Queries
query types relationships joins mulitable
queries creating a calculated field creating
summary queries
3
Query Types
On the design query table pane, right click and
the cascading window will appear.
4
There are 5 general query types

• Select
• crosstab
• parameter
• action
• SQL

5
Select

• Select query - retrieves the specific data you
  request from one or more tables, then displays
  the data in query datasheet in the order you
  specify.
• This is the most common type of query. (also the
  one we have concentrated on)

6
Crosstab

• Crosstab query - Summarizes large amounts of data
  in an easy-to-read, row-and-column format

7
Parameter

• Parameter query - Displays a dialog box prompting
  you for information, such as criteria for
  locating data.
• For example, a parameter query might request
  beginning and ending dates, then display all
  records matching dates between the two specified
  values

8
Action query

• Makes changes to many records in one operation.
• There are 4 types
• make-table - creates a new table from selected
  data in one or more tables
• update - makes update changes to record, such as
  when you need to raise the cost of material by 5
• append - add records from one or more tables to
  end of other tables
• delete - deletes records from a table or tables

9
SQL

• Created using SQL (Structure Query Language)
• an advanced programming language used in Access

10
Working with more than one table

• Large tables are difficult to manage
• most likely will have redundancy
• A join is an association that tells Access how
  data between tables is related
• A relationship is established between tables
  usually through at least one common field.

11
Related tables

• Tables that are related must share at least one
  common field
• The data type for the common field must be the
  same in tables being related
• you cannot relate a text field to a date/time
  field
• Often the fields have the same name, but this is
  not a necessary requirement.

12
Joining Tables

• To join tables, you first bring both tables to
  the upper pane of the design query window.
• Access will draw a line, called a join line,
  between matching fields from either table.
• Matching fields are fields with the same name
  where one of fields is a primary field.
• This is called a default join or an inner join.

13
Joining Tables

• If you fail to give the matching fields the same
  name, Access will not automatically insert the
  line and create the join.
• You can create the join manually by dragging form
  one common field to to the other.
• The join instructs the the query to check for
  matching values in joined fields.
• When matches are found, the matching data is
  added to the query datasheet as a single record

14
Three types of relationships

• one-to-many
• many-to-many
• one-to-one

15
One-to-many

• A record in table A can have many matching
  records in table B, but a record in table B have
  only one matching record in table A
• Example one instructor may teach one or more
  sections

16
Many-to-many

• A record in table A can have many matching
  records in table B, and a record in table B can
  have many matching records in table A
• Example Instructors can teach many courses.

17
Example of a many-to-many using a third linking
table
18
One-to-one

• A record in table A has only one matching record
  in table B, and a record in table B has only one
  matching record in table A.
• Example an employee information table and an
  employee compensation table

19
Defining

• using Tools-gtRelationships, you can define
  permanent relationships between tables that will
  enforce the rules of referential integrity

20
Two tables in a one-to-one relationship. When the
Employee ID fields of the two tables are joined,
a query can be created using data from both tables
Employees Enployee ID Hire Date Last Name First
Name ...
Location Employee ID Location Job Title
(Joined on common field)
21
Design Query
Customers Customer Id First Name Last Name Age
Credit Card Customer Id Credit limit ...
Display in alphabetical order the last names of
customers that have a credit card with us
Field
First Name
Last Name
Customer Id
Table
Customers
Credit Card
Customers
Sort
Ascending
Show
Criteria
Or
22
The Results

• A list will appear when the run command is
  clicked, the ! Icon.

First Name Adam Eve Susan John Joseph Harold
Last Name Abrahams Cane Duly Done Jackson Smith
Customer Id 14566 34564 56734 59432 57778 89576
Both tables must contain matching records in
order for a record to appear in the querys result

23
Calculated Field

• A calculated field enables you to draw the data
  form other field columns and perform a
  mathematical calculation on a row-by-row based
• Generally, if a value can be calculated for
  display, it should not be stored permanently in
  the database

24
Create

• Select an empty field column in the query design
  grid, click the expression builder to help you to
  enter expression. (the magic wand)
• example syntax
• Nameof field in datasheet windowexpression
• example NEW GRADE expression

25
Creating

• Select an empty field column in the query design
  grid, click the expression builder to help you to
  enter expression. (the magic wand)
• syntax
• Name of field in datasheet windowexpression
• Expression - can use a field name, operators and
  numbers
• example
• multiply the field purchase ( in table Customers)
  by 5 and call this field Tax
• TaxCustomers!purchase .05

26
Creating Summary Queries

• Enables to perform aggregate calculations to
  summarize a group of data.
• Right click on the design grid and a cascading
  menu will appear, the top will be be Totals.
  Select totals, an extra row will appear in the
  design grid called Totals
• select a cell in the Total row of the desired
  column.

27
Select a calculation option
28
First and Last

• The First and Last functions are most useful in
  calculated controls on a report. For example, if
  you have an Order report that is grouped on a
  ShipCountry field and sorted on an OrderDate
  field, you can use the First and Last functions
  in calculated controls to show the range of
  earliest to latest order dates for each grouping.

29
Questions