DB Implementation: MS Access Queries

1
DB ImplementationMS Access Queries Reports
2
Outline

• Access Queries
• Query Creation
• Sorting Filtering
• Query Types
• Dynamic Query
• Access Reports
• Report Sections
• Grouping Filtering

MS Access File for Lecture
3
MS Access Queries

• Database Queries
• Core DBA skill
• From SQL to Query by Example (QBE)
• What does it do?
• Find target information
• Retrieve, Filter, Sort, Aggregate/Summarize
• Manipulate data
• Perform calculations
• Add, Change, Delete, Combine data in tables
• Assemble/Supply data for forms and reports
• How does it work?
• Access translates QBE to SQL
• SQL performs data manipulations based on
  Relational Algebra
• Access queries create a dynaset (live view of
  table)
• changes made in data by query is reflected in
  underlying tables

4
Access Queries Views

• Datasheet view
• For displaying the result of the query
• Useful for reviewing/validating the query
• Design View
• For creating/modifying a query using drag drop
  GUI (i.e., QBE)
• Consists of Diagram Pane Grid Pane
• Add tables/queries to the Diagram Pane
• Add fields to the Grid Pane (Field row)
• Can sort/filter/compute by fields
• Sort row set to Ascending/Descending
• Criteria row use Expression to apply data filter
• Total row compute (sum, min, max, count, etc.)
  of each field
• Automatically generates SQL statements
• SQL View
• For creating/modifying a query by manually
  writing SQL statements
• Only way to create SQL-specific queries
• Union/Data-definition/Pass-through query

5
Access Queries Basic Types

• Simple Query
• Uses one table/query
• To generate a subset (row/column) of a table
• Multi-table Query
• Joins multiple tables/queries
• To merge small chunks of data in normalized
  tables
• Linked tables are automatically linked in in the
  Query Design Grid
• Creating a link in the Query Design Grid does not
  permanently link tables
• Select Query
• Selects records that meet given criteria
• Does not change the data
• Parameter Query
• Prompts for query criteria values (parameters) to
  run a dynamic query

6
Access Queries Sort Filter

• Datasheet View
• Sorting
• Click column and right-click
• Filtering
• Filter Tool (Home tab)
• Filter by Selection
• Filter by Form
• Advanced Filter/Sort
• Design View
• Sort using the Sort Row
• Sort priority is from left to right for multiple
  sort
• Filter using the Criteria Row
• Criteria in multiple rows make OR query
• Criteria in single row make AND query
• Criteria in a single cell
• AND/OR
• Wildcards () in Like and Between
• e.g. Like A, Like A-C, Like av, Like
  ave?, Not Like A

7
Access Queries Query Criteria

• Query Criteria Expressions

Expression Returns
Between 1/1/99 and 12/31/99 Dates from 1/1/99 to 12/31/99
In (John,Mary,James) Records with John, Mary, or James
Is Null Records with no entry
Like Acc?ss Acc followed by any character, followed by ss
Like s Ends in s
Like v Starts with v
lt1000 Less than 1000
1000 Equal to 1000
Like A-C?? Starts with A through C and has two more characters
???? Any four characters
Len(Surname) Val(4) Any Surname of 4 characters
Right(Surname,2) ss Any Surname ending in ss
Left(Surname,2) ac Any Surname starting with ac
8
Access Queries Expressions

• Using expressions to create a calculated column
• Enter expressions in blank column of query design
  view
• NAME Field1 operator Field2
• Format the display in Format property of the
  field
• Examples
• Simple math
• DiscountPrice Discount StandardPrice
• String Concatenation
• Name FirstName Lastname
• Date Time math
• DateDiff(Interval, BeginDate, EndDate)
• HireAge DateDiff(yyyy,BirthDate,HireDate)
• DateAdd(Interval, Number, Date)
• RetireDate DateAdd(yyyy,25,HireDate)
• Customized Sorting
• Switch(expr1, value1, expr2, value2, etc.)
• Switch(CitySeatle, 1, CityRedmond, 2,
  etc.)

9
Access Queries Dynamic Criteria

• Parameterized Queries
• Dynamic query based on varying criteria value
• e.g. retrieve books written by a given author
• Enter the parameter name in square brackets in
  criteria
• Do not use existing field names
• Can use expressions
• e.g. Like "" Last Name "
• Query Criteria from Form Entries
• Use a form to enter query parameter values
• good for multiple parameter entries
• Create a parameter query
• Create a form with unbound input control for each
  of query parameters
• Add a command button that will run the parameter
  query
• Run Query Action of Miscellaneous Category
• Reference the form controls that hold parameter
  values from the query
• i.e. change the criteria to form controls
• Parameter name fully qualified name of form
  control

10
Access Queries Joins

• Inner Join
• Default join in Access (i.e. Natural Join)
• Returns only the records where joined fields are
  equal in both tables
• Left Outer join
• Returns all records from the left table
• Right Outer join
• Returns all records from the right table

11
Access Queries Advanced Types

• Action Queries
• Append Query
• Appends table rows to an existing table
• Delete Query
• Deletes table rows
• Update Query
• Modifies the values of particular fields for
  particular records
• Make Table Query
• Creates a new table from rows of other
  tables/queries
• will overwrite existing table of the same name
• Crosstab Query
• Performs mathematical operations on intersection
  of two fields
• SQL-Specific Queries
• Data-definition Query
• Defines/Changes the definition of a database
  object (e.g., create/modify a table)
• Union Query
• Combines multiple SELECT queries

12
Access Queries Action Queries

• Append Query ? Appends table rows to an existing
  table
• Create a select query.
• Convert the select query to an Append query
• Append Tool in Query Type group of Design tab
• Choose the destination fields for each column in
  the append query.
• Run the query to append rows/records.
• Delete Query ? Deletes table rows
• Create a select query.
• Convert the select query to a Delete query.
• Delete Tool in Query Type group of Design tab
• Run the query to delete rows/records.
• Update Query ? Modifies the values of particular
  fields for particular records
• Create a select query.
• Convert the select query to an Update query.
• Update Tool in Query Type group of Design tab
• Set Update To (and Criteria when appropriate)
  rows
• Run the query to update fields/columns.

13
Access Queries Crosstab Query

• Crosstab Query ? Performs mathematical
  operations on intersection of two fields
• Using the Crosstab Query Wizard
• Create a select query to join tables (if needed).
• Click Query Wizard in the Other group of the
  Create tab.
• Select Crosstab Query Wizard in the New Query
  dialog box click OK.
• Choose table/query with which to create a
  crosstab query click Next.
• Choose the field(s) whose values will be used as
  row headings click Next.
• Choose the field whose values will be used as
  column headings click Next.
• Choose a field and a function to use to calculate
  summary values.
• The data type of the field that you select
  determines which functions are available.
• Using the Crosstab Tool
• Create a select query.
• Convert the select query to a Crosstab query
• Crosstab Tool in Query Type group of Design tab
• Set Row Headings
• Crosstab Row Heading, Total Group By, Sum,
  Avg, etc.
• Set Column Headings
• Crosstab Column Heading, Total Group By
• Set the calculation field and function

14
Access Queries SQL Queries

• Data-definition Query ? Defines/Changes the
  definition of a database object
• Create a table
• CREATE TABLE table_name ( field1 type(size),
  field2 type(size), , PRIMARY KEY (field))
• Modify a table
• ALTER TABLE table_name ADD/ALTER COLUMN field
  type(size) DROP COLUMN field
• Union Query ? Combines multiple SELECT queries
• Select queries must have the same fields (i.e.,
  Union compatible)
• SELECT field1, field2, . FROM
  table1UNIONSELECT fieldA, fieldB, . FROM
  table2
• Create the select queries in Design view
• Copy paste SQL statements into a union query
• Pass-through Query ? Send command directly to
  ODBC database server to run server-side SQL
• Configure the server DBMS as an ODBC data source
• Create a Pass-through query

15
MS Access Reports

• Access Reports
• Allows presentation of table and query data in a
  customized layout
• Good for data summarization
• Not a tool for data manipulation
• Optimized for printing rather than screen
  display
• Report Views
• Design View
• Provides a detailed view of report structure
• e.g., headers/footers for the report, page,
  groups
• Layout View
• Useful for modifying appearance and readability
  of the report
• e.g., setting column widths, adding grouping
  levels
• Print Preview
• How the report will look when printed
• Displays page breaks, report columns, etc.
• Report View
• For basic viewing the report
• Can copy data

16
Access Reports Creating Reports

• Using the Report Tool
• Select the source (table/query) of the report in
  the Navigation pane.
• Click the Report tool in the Reports group of the
  Create tab.
• Using the Report Wizard
• Click the Report Wizard in the Reports group of
  the Create tab.
• Follow directions on the Report Wizard pages.
• Using the Blank Report Tool
• Click the Blank Report tool in the Reports group
  of the Create tab.
• Drag fields from the Field List pane to the
  report

17
Access Reports Sections

• Report Header
• Appears once at the beginning of the report
  (before the page header)
• Use for Cover Page information
• e.g., report author, company logo, date
• Page Header
• Appears at the top of every report page
• e.g., report title
• Group Header
• Appears at the beginning of each group
• Use for group name
• e.g., company name when grouped by company
• Detail
• Main body of the report (table/query data area)
• Group Footer
• Appears at the end of each group
• Use for summary information for a group
• Page Footer
• Appears at the bottom of every page
• e.g. page numbers, dates

18
Access Reports Grouping Filtering

• Creating a grouping Level
• Access adds a header footer corresponding to
  grouping fields name
• e.g. grouped by Category ? Category Header,
  Category Footer
• Option in Report Wizard
• Manual group creation
• Click Group Sort tool in Grouping Totals
  group of Format/Design tab (Layout/Design view)
• Click Add a group in the Group, Sort, and Total
  pane.
• Select a field to group by
• Click More to set additional grouping options
• Filtering Records
• Using Select Query
• Set filtering conditions in query criteria
• Using Filter Properties
• set Filter and Filter On properties (Data tab) of
  a report
• Dynamic Criteria
• Use Parameter Queries

19
Access Reports Counting Summing

• Using the Layout View
• Open a report in Layout View
• Select the field you want to count
• Click Totals icon in Groupings Totals group of
  the Format tab
• Select an appropriate summary function
• Right-click the summary fields and click Set
  Caption
• Using the Design View
• Open a report in Design View
• Place a Text Box in appropriate section of the
  report
• Set the Control Source property of the text box
  to be an expression using a summary function
• Summary counts COUNT(Field_NAME)
• Sums SUM(Field_NAME)
• Set the Running Sum property if running total is
  desired