Querying a Database

1
Querying a Database

• A question or an inquiry (dictionary.com)

• WHAT ARE WE ASKING QUESTIONS ABOUT?

THE DATA

• BY ASKING QUESTIONS OF THE DATA WE OBTAIN?

INFORMATION
2
What do Access queries allow you to do?

1. Display selected fields and records from tables
2. Sort records
3. Perform calculations
4. Generate data for forms, reports and queries
5. Update data in the tables of a database
6. Find and display data from two or more tables

3
Select Queries

• Ask questions of data and retrieve results

• Types of results
• Entire Tables
• Certain Fields
• Data based on criteria

Criteria Meaning
Equal to (implies exact match)
lt , lt Less than / Less than or Equal To
gt, gt Greater Than / Greater Than or Equal To
ltgt Not Equal To
Betweenand Between value1 and value2 inclusive
In() In(value1, value2) contained in the list
Like Matches a pattern
4
Sorting Data

1. Alphabetically (A to Z) or (Z to A)
2. Numerically (Number or Currency)
3. Chronological Order (Date/Time)

• All data can be sorted either in ascending or
  descending order
• Sorting can be accomplished through the datasheet
  view or the query builder screen
• Ability to sort on two or more fields - Primary,
  Secondary, sort keys

5
Conditional Operators
AND - value1 AND value2 - requires that both
conditions are met to return a value OR - value1
OR value2 - requires that one or the other or
both conditions are met to return a value

• DoctorFName Luigi AND DoctorIQgt200
• PatientFName Frank or PatientLName Smith

6
Calculated Fields
Calculations Meaning
Avg Calculate the average of a column
Count Count the number of records
Max Obtain the Maximum Value of a column
Min Obtain the Minimum Value of a column
Sum Add the contents of a column
User Defined Create a new column based on a math formula
DoctorYearlySalary / 12 DoctorMonthlySalary 0.07
7
Lab Assignment

• TO DO Answer the following questions about the
  data
• List all doctors in the system
• List all patients in the system
• List all doctors whose last name begins with A
• List all appointments that have occurred in the
  last week in order of date
• List all appointments which occur today and show
  the patient, doctor, and hospital associated with
  the appointment along with date and time
• Show the number of appointments that have
  occurred in the last month

8
Lab Assignment cont.

• Now, assume that we want to keep track of each
  individual doctors yearly salary. Make the
  appropriate adjustment to your table and enter in
  the data.
• Answer the following questions
• - Compile a list of doctors in order of their
  yearly salary from highest to lowest
• - Calculate the average salary for a doctor in
  our system
• - If a doctor gets taxed 11 of his salary per
  month, calculate the total amount of tax the
  doctor will pay in one year