Databases Competency 7.00

1
DatabasesCompetency 7.00
Mastering Databases

• Objective 7.02
• Explain advanced database concepts and functions.

2
Retrieving Data
1. Open the database program
2. Select the desired database file and click
open.
3
Open a Table in a Database File
Select the proper table to open
4
Displaying Database Tables
Example DATASHEET VIEW of the MusicFun Table
from the TUNES database
5
Use Filters to Select Data

• Filter Displays records in a database that
  match specified criteria.
• Advantages of using filters
• Easier to use than query
• Fast
• Disadvantages of filters
• All fields are displayed when using a filter
• Cannot save filters like queries
• Types of filters
• By Form (key criteria into a form more flexible
  because criteria can be set for more than one
  field)
• By Selection (quick/easy option by highlighting a
  cell as criteria)
• Excluding Selection (excludes data highlighted in
  cell)
• Advanced Filter/Sort (works like a query)

6
Advanced Filter/Sort Menu
Choose the RECORDS Menu, FILTER
command, Advanced filter/sort
7
Build a Query

• Query A database object that allows the user
  to select records/data from a database using
  multiple criteria (comparison operators) and
  arrangement (sorting) standards.
• Advantages
• Queries can be saved
• Each table can be associated with multiple queries

8
Set Conditions for Query
Criteria Release Date gt 7/01/2004
Field Affected
Sort Order
9
Comparison Operators for Queries and Filters

• Equals
• Does not equal ltgt
• Between and between and
• Less than lt
• Greater than gt
• Equal to or less than lt
• Equal to or greater than gt

10
Query Advanced Criteria

• AND Condition Uses the same row in the Query
  Design window
• The AND condition reduces the number of records
  because both conditions must be met.
• OR Condition Uses different rows in the Query
  Design window
• The OR condition increases the number of records
  because records are selected if either condition
  is met.

11
Query AND Condition
Results
Criteria Genre Rock AND Release Date
after 2002
AND condition listed on ONE line
12
Query OR Condition
Results
OR Condition takes 2 lines!
Genre Country OR Rock And Release Date Greater
than 01/01/2000
13
Search Records

• To search for specific records, select Find
  command from the Edit menu.
• Key the search string (data) in the dialog box.
• Key where to find the records (which table).
• Similar to Find/Replace commands in other
  programs
• Not case sensitive

14
Sorting

• To sort is to arrange data in a specific order.
• Ascending sort Alphabetical order (A to Z) or
  numerical order (1 to 9)
• Descending sort Reverse alphabetical order (Z
  to A) and largest to lowest number (9 to 1)
• Simple sort Arranges by only one field
• Complex sort Arranges data by multiple fields
  (first field, primary second field, secondary)

15
Sort Using the Toolbar

• Ascending
• Descending

16
Complex Sort
17
Datasheet View Sort

• In Datasheet View, sorted fields must be
  highlighted and adjacent (sides touching).
• Sort priority sort left to right
• Leftmost column (field) primary sort
• Next field secondary sort

18
The Database Report

• A database object used to organize, summarize,
  and print all or some of the data in a database.

19
Advantages of a Report

• Data is viewed in an attractive format
• Allows variations in fonts, colors, shading, and
  borders
• Allows page layout design
• Allows images to be inserted
• Shows specific fields or selected records rather
  than an entire table
• Allows records to be grouped/sorted with
  summaries and totals
• Displays fields from multiple tables

20
Plan and Create a Report

• Determine the table/queries to be used in the
  report
• Determine what fields/data will be desired in the
  report
• Plan the groupings and summary totals if desired
• Enhance the appearance and design before printing

21
Determine the Fields Required
Using the Report Wizard, select the desired
fields for the report from the table or query.
22
Organize the Report with Grouping
This report will be grouped by name.
23
Sort Data in the Report
Sorting a field within the selected group will
further organize the report.
24
Design the Appearance
Follow the Wizards Next Steps to format the
design, style, and appearance of the report. Name
and preview the completed report.
25
Insert Pictures/Customize Database Reports
Insert pictures, labels, text boxes, and other
graphic elements while in Design View.
26
Database Relationships

• Relationship - A linked connection between two
  tables that share a common field.
• Join Line - graphically represents the
  relationship between two tables
• The related field between the two tables must be
  of the same data type and size

27
Establishing Relationships
Relationship established between two tables
One record in the Student Table is related to one
record in the Participation Table