Database Development using Microsoft Access CMPCP0015

1
Database Development using Microsoft
AccessCMPCP0015

• Lecture 6
• Queries, QBE SQL Part 1
• Mengjie Yu
• M.Yu_at_2001.ljmu.ac.uk

2
Recap

• In the last lecture
• Physical Database Implementation
• by use of MS-Access
• Table Definition
• Fields Definition
• Basic Data type
• Lookup Data type
• Set the Fields property

3
In this Lecture

• We will learn about-
• Imports data into MS-Access
• Imports the spreadsheet data into tables
• Relates Tables
• Defines the relationships among tables
• Query Design
• Queries in MS-Access
• Query-By-Example (QBE)
• Sets Selection Criteria / Calculation

4
Import Data into MS-Access

• Reuse of the existing data from other files
• Import data into existing MS-Access database
• Various tools to import data in different file
  formats
• File formats-
• Database dBase or MS-Access
• Spreadsheet MS-Excel, Lotus 1-2-3 etc
• Text files .txt files
• Our main interests
• Microsoft Excel Spreadsheet

5
Import a Spreadsheet

• Procedures of importing a spreadsheet data
• Opens the Access database
• Switch to the Database window
• Chooses the File Get External Data
• Choose Import from the submenu
• Selects the spreadsheet file for data importing
• Select the type of spreadsheet from Files of
  Type drop-down list
• Select the spreadsheet file via windows resource
  browser
• Clicks the Import button
• More details in Tutorial Exercise

6
Import a Spreadsheet
Heading
7
Relate Tables

• The Purpose of Relating Tables
• Retrieves data more efficient
• Speeds up the data searching
• Reduces the errors during data entry
• Ready for use in queries, forms, reports and data
  access pages.
• Defines relationships between tables
• When the tables are new and contain little or no
  data

8
Create A Relationship between Tables

• Create A Relationship with the Lookup Wizard
• The Lookup Wizard field data type
• Enters a list of values
• Retrieves the values from another table or query
  !!!
• For example the memberNo field
• The primary key in Member table and foreign key
  in RentalAgreement table

The Lookup data type
9
Create A Relationship between Tables

• Use the Relationships Window
• Relationships window
• a graphical tool to define and modify
  relationships
• Open the Relationships window
• click the Relationships icon on the Database
  toolbar
• Choose Tools Relationships from menu
• Add existing tables or queries into the
  Relationships window

10
Drawing the Relationship Line

• Drag a field and drop it on the corresponding
  field
• Usually the primary key linked with the foreign
  key
• Could be with different field names but
  definitely with the same data type and contain
  the same information

Child Table
Parent Table
Primary Key
Foreign Key
11
Drawing the Relationship Line

• The same Primary key can link with two or more
  foreign keys in the same table
• Automatically create the copies of Workerorders
  table

12
Referential Integrity

• Referential Integrity Option on the relationships
• Enforce Referential Integrity
• To keep a database complete and with no loose
  ends
• Two further options-
• Cascade Update Related Fields
• Value changes on the primary key in the parent
  table will be updated on the foreign key in the
  child table
• Cascade Delete Related Records
• Deleting a record in the parent table will
  deletes all the related child records

13
Join Type

• Specify how to associate records from related
  tables
• No affects on the existing relationships
• Just tells Access which records to include in a
  query result

14
Print Relationships

• To print the table
• relationships diagram-
• In the Relationships window, right-click in an
  empty area and choose Show all
• When all tables appear in the layout, choose
  File Print Relationships

15
Print Relationships

• To print the table relationships diagram-
• Choose Tools Analyze Documenter
• Click the Current Database tab and select
  Relationships
• Click Ok to print the relationships in the
  table form.

16
Query Design

• What are queries in the database?
• A set of explicit specifications for retrieving
  data from the database
• data / information can be stored for the later
  use !!!
• Data in a single table or multiple Related Tables
  or even existing queries
• Different types of queries in Access database-
• the popular query select query
• Used for extracting specific information from one
  or more tables
• the action queries insert, update, and delete
  records queries
• Perform global data management operation on
  tables

17
How Do Queries Work?

• Specifies fields appeared in the results
• Select the on-demand fields from existing tables
  or queries
• Sets the selection criteria to limit records in
  the results
• e.g. in the member table, lists all the male
  employees sexM
• Selects the desired order for records to appear
• e.g. sorts records in Ascending or
  Descending
• Defines calculation on the selected fields
• e.g. defines the summary for all the items sales
  prices

18
Create Select Queries

• Uses the Simple Query Wizard
• Uses a series of dialog boxes to specify the
  fields and records appeared in the results (e.g.
  queries)
• Defines the queries in Design Window

Table Panedisplays all the tables in the query
Design Grid QBE (Query By Example)
19
Run the Query

• Three ways to run the query-
• Click Datasheet view
• Click Run icon from the toolbar
• Choose Query Run from the menu

20
Set Selection Criteria

• Uses a single Criteria

Case 1
Case 2
memberNo 12
memberNo 12 or 14
21
Set Selection Criteria

• Uses multiple Criteria
• Applies more than one selection criteria
• Combines those criteria with AND or OR
  operator

(memberNo 12 AND dateOutlt 07/01/1999 ) OR
sexM
22
Set Selection Criteria
Another Example
(memberNo 12 AND fNameJohn ) OR (memberNo10
AND sexM)
23
Perform Calculations in Query

• Defines the calculation type on the specific
  field
• Calculation type such as total, average,
  min, max etc

Totals button
Select calculation type from this drop-down box
Design Grid
24
Add a Calculated Field

• Add a new field that displays the results of a
  calculation based on other fields in the grid
• NetProfit TotalTurnover TotalReturns

Calculation Expression
Field Name
New Field
Existing Fields
25
Summary

• Imports Data into Access
• Imports the spreadsheet data into tables
• Relates Tables
• Query Design
• Creates Selection Query
• Sets Selection Criteria
• Defines the Calculation in query
• Uses the Totals function
• Add the new field with the calculation results of
  other fields

26
Next Lecture

• In the next lecture -
• Create Advanced Queries
• Introduction to SQL (Structure Query Language)