Creating Queries
Introduction
Databases can store huge amounts of data.
When you have lots of data it is often hard to find what you are looking for.
Fortunately all database programs have ways of searching (or finding) and sorting the data they store. The process of doing this is called creating a query.
Query
A method of searching for data in a database.
Microsoft Access uses a method called Query-by-Example that allows you to enter the search criteria as well as save the query for a later date.
The explanations below all use the database 'CarData' which you can download to try to the queries too.
The database contains 100 records with the details of used cars.
Create a Query
To find and sort records in an Access database you need to create a 'Query' where you choose which fields you want to display, sort and search.
In the create tab, select 'Query Design'.
Then choose the table(s) you want to access data from, in this case the Cars table.
For the following examples you are only going to need the data stored in the Make, Model, Price when New, Price Now, Mileage and Age fields.
Either drag the fields from the cars table or use the drop-down menu in the "Field:" row of the query table to add the fields.
Close the query and save it as 'Query1'
Sorts
Using the Query1 from the example above, edit the query so that it sorts the data in the following ways:
1. Sort the list numerically from smallest to biggest (Ascending) by Price Now
2. Sort the list numerically from biggest to smallest (Descending) by Mileage
3. Sort the list alphabetically (Ascending) by Make
4. Sort the list in Ascending order by Age and by Price When New
Simple Finds
3a - Simple Finds
Create a new Query Form, and include the following fields: Make, Model, Price Now, Age, Mileage and Gearbox.
Save the query as 'Task 3a'
Carry out the following finds by typing the correct criteria into the query form.
Screenshot the query and results as evidence.
5. Find all Ford cars
6. Find all cars with a 4 speed gearbox
Conditional Finds
7. Find all cars with over 50,000 miles on the clock
8. Find all cars between 2 and 5 years old
Comparative Operators
A comparative operator is a mathematical symbol which is used to compare values in a query.
Logical Finds
And
Create a new Query Form, and include the following fields: Make, Model, Price Now, Age, style, Doors, Fuel, Central Locking and Airbags.
Save the query as 'Task 3b'
Carry out the following finds by typing the correct criteria into the query form.
Screenshot the query and results as evidence.
9. Find all Saloon cars with 4 doors
10. Find all Central Locking cars with airbags
11. Find all Diesel cars less than 4 years old
Or
Create a new Query Form, and include the following fields: Make, Model, Price Now, Colour, Style and Fuel.
Save the query as 'Task 3c'
Carry out the following finds by typing the correct criteria into the query form.
Screenshot the query and results as evidence.
12. Find all cars that are Red or Blue
13. Find all cars that are Utility or Estate
14. Find all cars that are either Diesel Citroens or Diesel Renaults
Not
Create a new Query Form, and include the following fields: Make, Model, Price Now, Colour, Style and Doors.
Save the query as 'Task 3d'
Carry out the following finds by typing the correct criteria into the query form.
Screenshot the query and results as evidence.
15. Find all cars that are not White
16. Find all Ford cars that are not Blue
17. Find all cars that are not 3 door
18. Find all Ford or Renault cars that are not Hatchback
Download:
- CarData MS-Access Database