Databases and Microsoft Access Query Examples

1
Databases and Microsoft AccessQuery Examples
2
Specifying Conditions in a Query

• You can specify an exact match condition, which
  allows you to specify that only records that
  match your criteria will be displayed.
• To specify an exact match, use the criteria row
  in your query design grid.
• When you run the query after specifying an exact
  match, only those records that meet the criteria
  will be displayed.
• An exact match condition specifies that the value
  in the specified field must match the condition
  exactly.

3
Exact Match Operators and Results
4
And and Or Logical Operators

• When you need to use multiple conditions for a
  query, you can use the logical operators to
  combine conditions.
• The And logical operator specifies that both
  conditions must be met
• The Or logical operator specifies that one or the
  other of the conditions must be met
• When you enter two conditions on the same row of
  the query design grid, an And condition is
  created.
• If you enter two conditions that are on separate
  rows, an Or condition is created.

5
And and Or logic
The And condition.
The Or condition.
6
And / Or Conditions in Design Grid
7
Perform Calculations in a Query

• Queries can be built to perform a calculation as
  part of the query.
• Expressions can be entered into the query design
  grid.
• Make certain that you are following the rules of
  precedence.
• To perform a calculation in a query, you must add
  a calculated field to the query design.
• You have three options for entering expressions
• Enter the expression directly into the field text
  box
• Enter the expression in the Zoom box
• Enter the expression in the Expression Builder

8
Expression Builder dialog box
9
Calculated Field in the Datasheet
Expression Builder adds your calculated field to
the query design grid. You can then assign it a
name, which will display in query datasheet view
when the query is run.
10
Using Aggregate Functions
Access has several Aggregate Functions that can
be used to calculate various statistical
information.
Aggregate functions are specified in the Total
row of the design grid. They can be assigned by
clicking the Totals button on the Query Design
toolbar.
11
Query Examples 1

• Query to display
• EmployerName, City, PositionTitle, Wage,
  Hours/Week, StartDate
• Sort by StartDate
• Limit to Hours/Week greater than 35

12
Query Examples 1

• Query to display
• EmployerName, City, PositionTitle, Wage,
  Hours/Week, StartDate
• Sort by StartDate
• Limit to Hours/Week greater than 35

13
Query Examples 2

• Query to display
• EmployerName, NAICSDesc, PositionTitle, Wage,
  Hours/Week, StartDate
• with Wage gt 15 AND starting date after 10/1/2004

14
Query Examples 2

• Query to display
• EmployerName, NAICSDesc, PositionTitle, Wage,
  Hours/Week, StartDate
• with Wage gt 15 AND starting date after 10/1/2004

15
Query Examples 3

• Query to display
• EmployerName, PositionTitle, Wage, Hours/Week,
  StartDate
• with Wage gt 20 OR starting date after
  12/1/2004
• Sort by start date

16
Query Examples 3

• Query to display
• EmployerName, PositionTitle, Wage, Hours/Week,
  StartDate
• with Wage gt 20 OR starting date after
  12/1/2004
• Sort by start date

17
Query Examples 4

• Query to display
• EmployerName, City, PositionTitle, Wage,
  StartDate
• all positions in Massachusetts that pay more
  than 18 per hour

18
Query Examples 4

• Query to display
• EmployerName, City, PositionTitle, Wage,
  StartDate
• all positions in Massachusetts that pay more
  than 18 per hour

19
Query Examples 5

• Query to display
• Employers and their addresses, whose name
  contains the word Resort

Note that the wildcard character is used
before and after the word Resort
20
Query Examples 5

• Query to display
• Employers and their addresses, whose name
  contains the word Resort

21
Query Examples 6

• Query to display Weekly wages for all positions
• Note that this is a calculated field which
  shows up as a new column
• Calculation is based on an expression entered
  as the field name

22
Query Examples 6
Query to display Weekly wages for all positions
Note The name of the expression used for the
calculated field will appear as the column name
in the query results.
23
Query Examples 7

• Query to compute Total number of positions,
  average Wage, and sum of all the Weekly Hours.
• This type of query is performed using
  aggregation functions
• You can get the Group by boxes added to the
  query design view by selecting the S icon from
  the menu bar. Clicking in the box allows you to
  choose from built-in functions such as Count,
  Sum, Avg, etc.

24
Query Examples 7
In this case, we select Count for PositionID (to
count the number of positions), Avg for the Wage
column, and Sum for the HourPerWeek column.
25
Query Examples 7
Query to compute Total number of positions,
average Wage, and sum of all the Weekly Hours. ?
The datasheet view will show a single row with
the computed values for three new fields
CountOfPositionID, AvgOfWage, and
SumOfHoursPerWeek.