Creating Queries

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

carData.accdb