Designing
Your First Database: 4
Queries
A Query is an operation which will allow you to extract information
from a table, or tables, given a particular criteria or condition.
For example, you could search for a stock item within a price
range or a person in a certain town.
Go
to the Query tab and select New, then Design View, then click
OK.
Example
1
For our first query we are going to look at all employees whose
salary is greater than £12000.
- When
the Show Table box appears, Click on Personnel and Add, then
select Staff Departments and Add.
- In
the Select Query box, click and drag Emp_Number from one table
to Emp_Number in the other. You will notice that a link has
been established between the two tables.
- Type
>12000 in the criteria row in the Salary field. (Don't
type in the £ sign, only the number).
- Run
the query by clicking on the Run button. Check your results
against the data in the tables.
- Close
the Query and save as 'Salaries over £12000'.
- Print
out your search.
Example
2
In our second query we want to find out which employees are due
for a review in March. Use the fields as shown below for the search.
Whenever you type in text as search criteria, Access automatically
adds quotes around it.
Run
the query and print out the results. Close and save it as 'March
Review'.
Example
3
Try this next query where we are looking for an employee whose
name begins with S and works in the Administration department
(Adm). When we are not sure of the spelling of a name or word,
we can use the asterisk - * - in the search criteria, as a 'wild
card'.
The
example below will find any Surname beginning with - S. (Access
will add the word Like and the quotes - "). Typing in a specific
name like - Smith - will only find Smith. Use the fields below,
save and print out your data.
Example
4
The next query is different in that we looking for staff whose
names do not begin with a C or a J, and also work in the Finance
department (Fin). The capture below shows you how to structure
the search. Note that the keyword - AND - is used instead of -
OR -. Print out your results.
Example
5
This search involves looking for all members of staff earning
over £20000 but having worked for the company for over 10 years.
Print out the search results. Close and save your search.
You
will note that the show boxes for the fields Service and Salary
have been unchecked, so only First Name and Surname will appear
in the search results. Note: only numbers are used in the search
- no £ signs.
Example
6
The next query is a search based on date of birth. Use the fields
and criteria shown. Print out the resulting data.
Example
7
We are going to use criteria in this next query to find data in
a range of numbers. In the First Name field we are going to put
our results in Ascending order using the Sort row.
Now
try some Queries of your own!
 Contents:
Databases
Skill
Check: Databases
|
