Unit 7.5

Murder Most Horrid (Exploring databases)

Tutorial 4:Solving the crime

Introduction

Work through Opener 4 as an introduction to this tutorial.

Detective Turner would like to thank you for creating the database for him. He has now asked one of his police officers to enter all the suspects' details into the database.

By the end of this tutorial you will be able to:

• Search the database using filters to find the murderer
• Create parameter queries to find specific information

In this tutorial, you are working on the following APP Assessment Focuses:

AF1

Level 4 – Plan and implement solutions that combine and refine different forms of information
– Evaluate the quality and success of your solutions

AF2

Level 4 – Organise and process data for a purpose

Level 5 – Use logical and appropriate structures to organise and process data

AF3

Level 4 – Use appropriate search criteria to find relevant information, and check its plausibility and usefulness

Level 5 – Take account of accuracy and potential bias when searching for and selecting information

Get started

Filter By Selection

The investigation is going really well and lots of witnesses have come forward. Detective Turner feels confident that he can solve the crime with your help. He needs you to search the database for him to find the information that he needs.

You are now going to find the following information using the filter: All suspects who live in Swinton. Find out how to do this by watching Skills Demo 4a.

Now it's your turn. Open Resource 4b to open the Crime database. Choose tblsuspects, then use filters to find all the people who live in Swinton.

Tips To remove the filter and go back to the whole table, choose Records > Remove Filter/Sort.

 

Now try writing down a set of instructions for using filters to test your memory and understanding. Ask a partner to follow your instructions to check they work. Did you remember everything?

Try it out

You are now going to use filters in the database to find and catch the murderer!

Finding the murderer!

Open Resource 4a and read the information on catching a murderer.

Complete Searches 1 and 2 on the worksheet. Don't remove the filter after Search 1! Remember to save your work.

Using the 'Advanced Filter' function

In order to find some of the information you need, you will have to filter using two different fields – in this case, to find people who were in the Maths and English departments. To do this you need to use the Advanced Filter function. You can see how to do this by watching Skills Demo 4b.

Now it's your turn. Use the 'Advanced Filter' function to find out who were in the English and Maths departments.

You now need to add the field that you want to search to the grid. The grid should already have the fields 'Shoe Size' and 'Hair Colour'. This is because you have already searched using these fields. Click in the field next to 'Hair Colour' and add the field 'Alibi' to your grid using the dropdown menu (see below).

Next, enter 'Maths Department' and 'English Department' in the 'Criteria' section (see below). Now apply the 'Advanced Filter' to your database. Don't forget to write down the names of the suspects on Resource 4a under Search 3.

Complete the worksheet to find the identity of the murderer!

Parameter queries

The murderer has now been arrested and Detective Turner needs to track down witnesses to appear in court. What information do you think he will need? Try to find a specific person by their surname (for example, Hirst). Did it work? What is the best way to get the information Detective Turner needs?

Detective Turner would like you to create a method of searching for people by their surname so that he can find the correct witness details ready for court. You will need to create a parameter query for this.

Parameter queries allow you to enter different criteria each time for the same search. So, if you need to be able to find people by surname, then a parameter query is ideal. Watch Skills Demo 4c to find out how to do this.

Now it's your turn. To create a parameter query that will find people using their surname you will need to include the following fields in your database: 'Surname', 'Forename', 'House number', 'Street', 'Town', 'Telephone number'.

Now test that it works by running the query and entering the surname 'Johnson'. Save the query as Surname. This means you will be able to use the query again and again.

Round it up

As a class, discuss the difference between filtering for information and using a query. Which method do you think is better? Would you have been able to find the murderer using a series of queries?

Filters are better at selecting multiple criteria while queries are better at finding objects or people based on one or two different criteria.

Homework

Write a list of bullet points describing each step that you took to find the murderer. Make sure you use key terms correctly.