Access Tutorial 5 Creating Advanced Queries and Enhancing Table Design

1
Access Tutorial 5Creating Advanced Queries and
Enhancing Table Design
2
Reviewing the Panorama Database
3
Using a Pattern Match in a Query

• A pattern match selects records with a value for
  the designated field that matches the pattern of
  the simple condition value
• The Like comparison operator selects records by
  matching field values to a specific pattern that
  includes one or more of these wildcard
  characters asterisk (), question mark (?), and
  number symbol ()
• Figure 5-4

4
Pattern matching

• Figure 5-4

5
Using a List-of-Values Match in a Query

• A list-of-values match selects records whose
  value for the designated field matches one of two
  or more simple condition values
• The In comparison operator lets you define a
  condition with a list of two or more values for a
  field
• Figure 5-6

6
Using the Not Operator in a Query

• The Not logical operator negates a criterion or
  selects records for which the designated field
  does not match the criterion
• Figure 5-7

7
Record Selection Based on Matching a list of
Values

• Figure 5-6 Figure 5-7

8
Using an AutoFilter to Filter Data

• Figures 5-8 and 5-9

9
Assigning a Conditional Value to a Calculated
Field

• The (ampersand) operator is a concatenation
  operator that joins text expressions
• The IIf (Immediate If) function assigns one value
  to a calculated field or control if a condition
  is true, and a second value if the condition is
  false
• The IsNull function tests a field value or an
  expression for a null value if the field value
  or expression is null, the result is true
  otherwise, the result is false
• Figure 5-12

10
Creating a Parameter Query

• A parameter query displays a dialog box that
  prompts the user to enter one or more criteria
  values when the query is run
• Create a select query that includes all fields to
  appear in the query results. Also choose the sort
  fields and set the criteria that do not change
  when you run the query
• Decide which fields to use as prompts when the
  query runs. In the Criteria text box for each of
  these fields, type the prompt you want to appear
  in a message box when you run the query, and
  enclose the prompt in brackets

11
Creating a Parameter Query

• Figures 5-15 and 5-16

12
Creating a Parameter Query

• Figures 5-18

13
Creating a Crosstab Query

• A crosstab query performs aggregate function
  calculations on the values of one database field
  and displays the results in a spreadsheet format
• Aggregate functions used in crosstab queries
• Avg
• Count
• Sum
• Min
• Max

14
Creating a Crosstab Query

• In the Other group on the Create tab, click the
  Query Wizard button
• In the New Query dialog box, click Crosstab Query
  Wizard, and then click the OK button
• Complete the Wizard dialog boxes to select the
  table or query on which to base the crosstab
  query, select the row heading field (or fields),
  select the column heading field, select the
  calculation field and its aggregate function, and
  enter a name for the crosstab query

15
Creating a Crosstab Query

• Query Wizard

16
Creating a Crosstab Query

• Crosstab Query Wizard

17
Creating a Crosstab Query

• Choose column, row, field, and function on field

18
Creating a Find Duplicates Query

• A find duplicates query is a select query that
  finds duplicate records in a table or query
• In the Other group on the Create tab, click the
  Query Wizard button
• Click Find Duplicates Query Wizard, and then
  click the OK button
• Complete the Wizard dialog boxes to select the
  table or query on which to base the query, select
  the field (or fields) to check for duplicate
  values, select the additional fields to include
  in the query results, enter a name for the query,
  and then click the Finish button

19
Creating a Find Duplicate Query

• Query Wizard

20
Creating a Find Duplicate Query

• Choose a table or a query

21
Creating a Find Duplicate Query

• Choose duplicate value field

22
Creating a Find Duplicate Query

• Choose additional fields to show

23
Creating a Top Values Query

• The Top Values property for a query lets you
  limit the number of records in the query results
• Create a select query with the necessary fields
  and sorting and selection criteria
• In the Query Setup group on the Query Tools
  Design tab, enter the number of records (or
  percentage of records) you want selected in the
  Return (Top Values) text box

24
Creating a Top Values Query
25
Creating a Lookup Field

• A lookup field lets the user select a value from
  a list of possible values
• You use a Lookup Wizard field in Access to create
  a lookup field in a table

26
Creating a Lookup Field

• Choose a field from a table (design view)

27
Creating a Lookup Field

• You must delete the all relations to that field
• Remember to add relations back after you finished
  creating a lookup field

28
Creating a Lookup Field

• Lookup Wizard

29
Using the Input Mask Wizard

• A literal display character is a special
  character that automatically appears in specific
  positions of a field value users dont need to
  type literal display characters
• Input mask

30
Using the Input Mask Wizard

• Choose table, field, and click Input Mask Build
  button ()

31
Using the Input Mask Wizard

• Input Mask Wizard

32
Defining Data Validation Rules

• To prevent a user from entering an incorrect
  value in the Zip field, you can create a field
  validation rule
• The Validation Rule property value specifies the
  valid values that users can enter in a field
• The Validation Text property value will be
  displayed in a dialog box if the user enters an
  invalid value

33
Data Validation Rules

• Choose table, field, and
• Enter rule in Validation Rule
• Enter warning message in Validation Text

34
Table Validation Rule

• A table validation rule compares one field value
  in a table record to another field value in the
  same record to verify their relative accuracy
• Steps
• Open table in design view
• Click Property Sheet
• Enter rule in Validation Rule text box
• Enter warning message in Validation Text text
  box

35
Table Validation Rule

• Open table in design view

36
Special Topic SQL
37
SQL

• SQL is an a standard computer language for
  accessing and manipulating database systems, such
  as MS Access, DB2, Informix, MS SQL Server,
  Oracle, and Sybase.
• there are many different versions of the SQL
  language, but to be in compliance with the ANSI
  standard, they must support the same major
  keywords in a similar manner (such as SELECT,
  UPDATE, DELETE, INSERT, WHERE, and others).

38
SQL Data Manipulation Language

• SELECT - extracts data from a database table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a database
  table

39
SQL Data Definition Language

• CREATE TABLE - creates a new database table
• DROP TABLE - deletes a database table

40
SQL The SELECT Statement

• The SELECT statement is used to select data from
  a table. The tabular result is stored in a result
  table (called the result-set).
• Syntax
• SELECT column_name(s)
• FROM table_name

41
Select Example
SELECT LastName, FirstName FROM Persons
42
Select All Columns

• To select all columns from the "Persons" table,
  use a symbol instead of column names, like
  this
• SELECT FROM Persons

43
The SELECT DISTINCT Statement

• The DISTINCT keyword is used to return only
  distinct (different) values.

Syntax SELECT DISTINCT column_name(s) FROM
table_name
44
Select Without Distinct Keyword
SELECT Company FROM Orders
45
Select With Distinct Ketword
SELECT DISTINCT Company FROM Orders
46
Where clause

• The WHERE clause is used to specify a selection
  criterion

Syntax SELECT column FROM table WHERE column
operator value
47
Operators used with Where

• Note In some versions of SQL the ltgt operator may
  be written as !

48
Using the WHERE Clause

• To select only the persons living in the city
  "Sandnes", we add a WHERE clause to the SELECT
  statement
• SELECT FROM Persons WHERE City'Sandnes'

49
Using Quotes

• SQL uses single quotes around text values.
  Numeric values should not be enclosed in quotes.

This is correct SELECT FROM Persons WHERE
FirstName'Tove' This is wrong SELECT FROM
Persons WHERE FirstNameTove
50
The LIKE Condition

• The LIKE condition is used to specify a search
  for a pattern in a column.

Syntax SELECT column FROM table WHERE column LIKE
pattern

• A "" sign can be used to define wildcards
  (missing letters in the pattern) both before and
  after the pattern.

51
Using LIKE

• The following SQL statement will return persons
  with first names that start with an 'O'
• SELECT FROM Persons
• WHERE FirstName LIKE 'O'
• The following SQL statement will return persons
  with first names that end with an 'a'
• SELECT FROM Persons
• WHERE FirstName LIKE 'a'

52
Using LIKE

• The following SQL statement will return persons
  with first names that contain the pattern 'la'
• SELECT FROM Persons
• WHERE FirstName LIKE 'la'

53
The INSERT INTO Statement

• The INSERT INTO statement is used to insert new
  rows into a table.

Syntax INSERT INTO table_name VALUES (value1,
value2,....)

• You can also specify the columns for which you
  want to insert data
• INSERT INTO table_name (column1, column2,...)
• VALUES (value1, value2,....)

54
Insert a New Row

• And this SQL statement
• INSERT INTO Persons
• VALUES ('Hetland', 'Camilla', 'Hagabakka 24',
  'Sandnes')

55
Insert Data in Specified Columns

• SQL statement
• INSERT INTO Persons (LastName, Address)
• VALUES ('Rasmussen', 'Storgt 67')

56
The Update Statement

• The UPDATE statement is used to modify the data
  in a table.
• Syntax
• UPDATE table_name
• SET column_name new_value
• WHERE column_name some_value

57
Update one Column in a Row

• We want to add a first name to the person with a
  last name of "Rasmussen"
• UPDATE Person SET FirstName 'Nina'
• WHERE LastName 'Rasmussen'

58
Update several Columns in a Row

• We want to change the address and add the name of
  the city
• UPDATE Person
• SET Address 'Stien 12', City 'Stavanger'
• WHERE LastName 'Rasmussen'

59
The Delete Statement
The DELETE statement is used to delete rows in a
table. Syntax DELETE FROM table_name WHERE
column_name some_value
60
Delete a Row
"Nina Rasmussen" is going to be deleted DELETE
FROM Person WHERE LastName 'Rasmussen'
61
Delete All Rows

• It is possible to delete all rows in a table
  without deleting the table. This means that the
  table structure, attributes, and indexes will be
  intact
• DELETE FROM table_name
• Or
• DELETE FROM table_name

62
SQL Create Table Statement

• CREATE TABLE table_name(column_name1
  data_type,column_name2 data_type,column_name3
  data_type,....)

63
MS Access data type
64
SQL Drop table statement

• Delete tables
• DROP TABLE table_name

65
Homework Assignment

• Read textbook Chapter 5
• Browse SQL Tutorial
• http//www.w3schools.com/SQl/default.asp