7X Period 5 Wednesday 13th June 2012

STARTER

What do you know about databases?  Click HERE to find out.

TASK 1 Printing your database table.

Open your CRIME INVESTIGATION NAME database.  Open your tblsuspects table.  Ensure that you have added your data / information as RECORD 1.  Print your table to the KC2mono printer.

TASK 2 Saving your database to the General area.

Ensure that your CRIME INVESTIGATION NAME database is closed.  Navigate to your network area and find your CRIME INVESTIGATION NAME database.  Right click and choose COPY.  Navigate to the folder 7X CRIME INVESTIGATION DATABASE.  Right click and choose PASTE.  Here is the path to teh 7X CRIME INVESTIGATION DATABASE folder - S:\General\ICT\year 7\7X 2011 – 2012. 

Task 3 – Creating queries in MS Access

1. Navigate to Shared:\Pupils\Senior Pupils\ICT\Y07\Unit 7.5 Murder Most Horrid\Lesson_1 and copy the file called SHR Pupils 2011-2012
2. When the database has opened, click on the ENABLE CONTENT buttton at the top in the middle and then save the database in your Unit 7.5 – Murder Most Horrid folder.
3. Then click on the table called All Girls 2011-2012 on the left hand side and see if you can find your name…(you might have to explore and experiment)

Watch this VIDEO to see how to build a simple query.

After the demonstration, try to produce the following queries. Save each query with a sensible name;

1. Find all the girls in your form and sort the list by Surname (a) also show firstname
2. Find all the girls in your house and sort the list by Year (a) also show firstname and surname
3. Find all the girls in the school called Eleanor and sort the list by Form Group (a) and then Age (a) show all fields
4. Find all the girls in your science group and sort the list by Form Group (a) and then House (a) also show surname and firstname
5. Find all the girls in the school who were born before 01/01/1994 also show surname and firstname
6. Find all the girls who have a surname which begins with D also show firstname and form (follow this link for help on this query – scroll down and find the Using Wildcards link)
7. Further queries – can you find all the girls in your form who have a “T” in their surname, can you find all the girls in your house whose firstname ends in an “R”?
8. Make up some queries of your own – what do you want to find out?

Task 3 – Reports

Queries help you find information but reports allow you to have more control over the presentation.  You can add logos, change the font style and size as well as adding colour.

Watch this VIDEO to see how to create a report.

After you have been shown how create a report, produce the following reports;

1. A list of girls in your form with the heading “Form List” and the Form at the top of the page.  The list should also show Surname, Firstname and DOB
2. A list of girls in your science group which has a heading “Science Group List” and the Science group at the top of the page.  The list should only show Surname, Forename, Form Group and House.  It should be ordered by  Form Group and then House.

EXTENSION

If you are completely finished try any of the ACCESS quizzez HERE.

7Y – Period 1: Wednesday 23rd May

Database Assessment

Please see the sheet you have been given for the assessment instructions.

If you finish before the end of the lesson then try any of the activities on this page: http://www.reviseict.co.uk/games/index.shtml?searchQuery=access

7W Period 3 Friday 12th June 2012

7.5 Lesson 3 Murder Most Horrid – Creating a Database. 

 

LEARNING OBJECTIVES

In today’s lesson you will learn how to;

• Make a database and check data for errors. 

 

Detective Turner and his team of detectives have been interviewing people.  They have now interviewed everyone using your questionnaire.  This information is taking a long time to sort through, and the team are having difficulties finding the information they need. 

Starter:

Read all about data types by clicking HERE.  Then a short quiz.

Task 1 Design a Table and create Field Name. 

Open the word document Field Names and Data Types (Reso3c).  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the dialogue box.  Call it Field Names and Data Types. 

First you need to design a table for the database into which you will enter all of the information. 

Remember that each question needs to be converted into a Field Name so that we can store this information in the database.  Using your questionnaire and the worksheet Field Names and Data Types, create a list of Field Names and Data Types for the database.  For example: for the question What is your surname? the best field name is simply Surname.  The data types you can use are listed in the box to the right of the table.  Any field that contains letters should be made into a text field.

 

Task 2 Setting up a database

Now you need to set up a database.  This means you need to open a blank database, so that you can create your own table. 

Open MS Access and set up a blank database.  Save this into your Unit 7.5 Murder Most Horrid folder.  Name your database Crime Investigation Database. 

Watch this VIDEO to help you with this task. 

 

Task 3 Create your Table. 

Once the database has been set up and saved, you can begin to create the table. 

Watch this VIDEO to help you with this task. 

Choose Create table in design view. 

Using the list of field names and data types you can created in Task 1, enter these into your table.  You can choose the Data Type you want by selecting from the drop-down menu. 

Importantly, the first Field Name you should enter is ID.  Give it the data type AutoNumber. 

Watch this VIDEO to help you with this task. 

Watch this VIDEO to help you with this task. 

 

Task 4 Create the Primary Key field. 

The ID field needs to be made into the Primary Key.  This will make sure that each person has a unique number (like a sort of membership number). 

To create the Primary Key: 

• Place your cursor in the field ID by clicking in the row. 
• Then click on the Primary Key icon on the toolbar. 

This field is now the Primary Key. 

Watch this VIDEO to help you with this task

 

Task 5 Save your database table. 

When you have entered all your Field Names and Data Types, you need to save the table. 

Choose File / Save. 

You will be asked to type in the name of your table. 

Type the name tblsuspects and click OK. 

 

Task 6 Enter your Information. 

Now you can enter the information from your buddies.   

To enter data you need to change the view from Design View to Datasheet View. 

To do this, click on the icon on the top toolbar.  When you have entered all the information, save the table again. 

Watch this VIDEO to help you with this task. 

 

Task 7 Check for Errors. 

It is easy to make a mistake when entering data.  One way to make sure that the information you enter is correct is to get someone else to look at it. 

Print your table by choosing File / Print.  Then swap printouts with your buddy, and: 

1. Circle any errors that have been made. 
2. Once you have checked the printout, give it back to the person to whom it belongs. 
3. Using the corrected printout of your table, correct your mistakes in the database. 

If you have finished try any of these ACCESS quizzes by clicking HERE.  

 

Extension Work – Database Bingo. 

I am going to read out one statement at a time and you are going to cross out the correct answer on the bingo cards I have given you. 

 

7X Period 5 Wednesday 23rd May 2012

7.5 Lesson 3 Murder Most Horrid – Creating a Database. 

 

LEARNING OBJECTIVES

In today’s lesson you will learn how to;

• Make a database and check data for errors. 

 

Detective Turner and his team of detectives have been interviewing people.  They have now interviewed everyone using your questionnaire.  This information is taking a long time to sort through, and the team are having difficulties finding the information they need. 

Starter:

Read all about data types by clicking HERE.  Then a short quiz.

Task 1 Design a Table and create Field Name. 

Open the word document Field Names and Data Types (Reso3c).  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the dialogue box.  Call it Field Names and Data Types. 

First you need to design a table for the database into which you will enter all of the information. 

Remember that each question needs to be converted into a Field Name so that we can store this information in the database.  Using your questionnaire and the worksheet Field Names and Data Types, create a list of Field Names and Data Types for the database.  For example: for the question What is your surname? the best field name is simply Surname.  The data types you can use are listed in the box to the right of the table.  Any field that contains letters should be made into a text field.

 

Task 2 Setting up a database

Now you need to set up a database.  This means you need to open a blank database, so that you can create your own table. 

Open MS Access and set up a blank database.  Save this into your Unit 7.5 Murder Most Horrid folder.  Name your database Crime Investigation Database. 

Watch this VIDEO to help you with this task. 

 

Task 3 Create your Table. 

Once the database has been set up and saved, you can begin to create the table. 

Watch this VIDEO to help you with this task. 

Choose Create table in design view. 

Using the list of field names and data types you can created in Task 1, enter these into your table.  You can choose the Data Type you want by selecting from the drop-down menu. 

Importantly, the first Field Name you should enter is ID.  Give it the data type AutoNumber. 

Watch this VIDEO to help you with this task. 

Watch this VIDEO to help you with this task. 

 

Task 4 Create the Primary Key field. 

The ID field needs to be made into the Primary Key.  This will make sure that each person has a unique number (like a sort of membership number). 

To create the Primary Key: 

• Place your cursor in the field ID by clicking in the row. 
• Then click on the Primary Key icon on the toolbar. 

This field is now the Primary Key. 

Watch this VIDEO to help you with this task

 

Task 5 Save your database table. 

When you have entered all your Field Names and Data Types, you need to save the table. 

Choose File / Save. 

You will be asked to type in the name of your table. 

Type the name tblsuspects and click OK. 

 

Task 6 Enter your Information. 

Now you can enter the information from your buddies.   

To enter data you need to change the view from Design View to Datasheet View. 

To do this, click on the icon on the top toolbar.  When you have entered all the information, save the table again. 

Watch this VIDEO to help you with this task. 

 

Task 7 Check for Errors. 

It is easy to make a mistake when entering data.  One way to make sure that the information you enter is correct is to get someone else to look at it. 

Print your table by choosing File / Print.  Then swap printouts with your buddy, and: 

1. Circle any errors that have been made. 
2. Once you have checked the printout, give it back to the person to whom it belongs. 
3. Using the corrected printout of your table, correct your mistakes in the database. 

If you have finished try any of these ACCESS quizzes by clicking HERE.  

 

Extension Work – Database Bingo. 

I am going to read out one statement at a time and you are going to cross out the correct answer on the bingo cards I have given you. 

 

7W Period 2 Tuesday 22nd May 2012

7.5 Lesson 2 Murder Most Horrid – Collecting the evidence. 

 

LEARNING OBJECTIVES

In today’s lesson you will learn how to;

• Design a questionnaire to collect witness information. 

Starter: Access Fling The Teacher - first flinger get a house point!

WORD WALL – Grab a handset.  Press any button to wake your handset up.  What do you know about Access? 

 

The police have been collecting lots of information from witnesses.  This information is getting in a mess and some has been lost. 

Detective Turner needs you to design a questionnaire that each police officer can take with them and fill out when questioning a witness or suspect. 

 

What information do you need to solve a crime?  WHITE HAT

Work in your groups.  Grab a white board, pen and cloth.  Jot down a list of information that the police might need to solve a crime.  HINT: People’s whereabouts, alibis and physical descriptions. 

 

Task 1 – What questions do we need to ask? 

The report from the crime scene investigators is now complete.  You will need to read this so that you know what type of questions you need to write. 

Open the word document Crime Scene Results (Reso2c) LESSON 2.  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the header (View / Header and Footer).  Highlight any information that you think is important. 

 

Task 2 – Write your questions. 

Detective Turner needs to find out who the murderer is and bring them to justice.  He can only do this by interviewing everyone who was at the school on the day of the murder and then eliminating them. 

Open the word document Collecting Evidence (Reso2a) LESSON 2.  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the name dialogue box. 

Work in your groups.  Write down a list of questions Detective Turner will need the answers to if he is to solve the crime. 

 

Task 3 Test your questions.  (BUDDY GROUPS)

It is very important that the questionnaire works, otherwise the murderer might get away.  Test your questions on some one to make sure that your questions gather the information you need. 

In your groups ask each other the questions that you have designed.  Write the answers to the questions on the worksheet. 

 

Task 4 Rewording questions

Watch the PowerPoint presentation Questions (Reso2b) LESSON 2.  On Slide 3 you can see a list of the information Detective Turner needs to find out.  Go back to your list of questions.  It is very important that the questions are worded correctly and in a way that allows the correct information to be given. 

Look at your questions.  Have you asked the right questions?  Do you think that they will get you all the information you need?  Rewrite your questions and add any further questions if necessary. 

 

Task 5 Put questions into a questionnaire. 

Detective Turner would now like the questions put into a questionnaire.  Slide 4 and 5 show you how responses can be guided.  Remember that different types of questions can have different types of responses.  The question: What is your surname? could have millions of answers, whereas What colour hair do you have? will get a response that can be chosen from a list, as most people have one of four hair colours. 

Slide 6 and 7 show how questions can be written in a questionnaire format using a word processor or a spreadsheet.  Using MS Word, create your questionnaire.  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the header.  Call it Murder Questionnaire.  You will need to use your worksheet Collecting Evidence. 

 

Task 6 Carry out a survey using your questionnaire. 

Detective Turner is very pleased with your finished questionnaire, and would now like you to interview three classmates. 

Print out three copies of your questionnaire to the KC2mono printer. 

Ask your classmates to fill them in. 

 

Extension Work

Annotate (write on) your questionnaires to explain why you have chosen to include each question and selected a particular method of response.  Remember it is good practice to use checkboxes and limit people to a few answers, because it makes it harder to give wrong information. 

 

 

7Y – Period 3: Wednesday 16th May

Starter: Read all about data types by clicking here.  Then a short quiz

Task 1 – Creating a database

Today you are going to create a database which will hold data about your classmates.

The data that is going to be recorded is;

FriendID, Firstname, Surname, House, ShoeSize, FavouriteSubject, FavouriteTeacher, PocketMoney (per month)

With the person next to you, can you identify the data types that should be used for each of the fields above?

After you have been shown how to create a new database and your have set up your friend table, enter the field names in design view.  Use the videos below to help you set up your fields.

Entering field names: http://www.teach-ict.net/software/access_2007/tables/enter_fieldnames_designview.htm

See if you can set up the correct field types as well, if you need some help watch this: http://www.teach-ict.net/software/access_2007/tables/datatypes_designview.htm

Task 2 – Collecting Data

Change the view of your Friend table to datasheet view.  Now go and enter your details in at least 10 of your friends databases.

Task 3 – Creating queries

Can you create queries in your database to find the following? (Save them with sensible names)

1. All your friends that are in a certain house?
2. All your friends that have a certain shoe size?
3. All your friends who have a certain favourite subject?
4. All your friends who have a certain favourite teacher?

Task 4 – Creating reports

Choose one of the queries you have created and create a report based on that query.  Format the report so it is neatly presented.  Can you add the school logo to the top of the report?  Add your name to the bottom of the report (remember to use the Label tool) and then print off your report.

Challenge Corner

1. Can you create a report which shows all your friends (use the table not a query) and groups them by the amout of pocket money they get each month?
2. Can you create a report which shows all your friends (use the table not a query) and groups them by the house they are in?

 

7X Period 5 Friday 18th May 2012

7.5 Lesson 2 Murder Most Horrid – Collecting the evidence. 

 

LEARNING OBJECTIVES

In today’s lesson you will learn how to;

• Design a questionnaire to collect witness information. 

Starter: Access Fling The Teacher - first three flingers get a house point!

 

The police have been collecting lots of information from witnesses.  This information is getting in a mess and some has been lost. 

Detective Turner needs you to design a questionnaire that each police officer can take with them and fill out when questioning a witness or suspect. 

 

What information do you need to solve a crime? 

Work in your groups.  Grab a white board, pen and cloth.  Jot down a list of information that the police might need to solve a crime.  HINT: People’s whereabouts, alibis and physical descriptions. 

 

Task 1 – What questions do we need to ask? 

The report from the crime scene investigators is now complete.  You will need to read this so that you know what type of questions you need to write. 

Open the word document Crime Scene Results (Reso2c).  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the header (View / Header and Footer).  Highlight any information that you think is important. 

 

Task 2 – Write your questions. 

Detective Turner needs to find out who the murderer is and bring them to justice.  He can only do this by interviewing everyone who was at the school on the day of the murder and then eliminating them. 

Open the word document Collecting Evidence (Reso2a).  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the name dialogue box. 

Work in your groups.  Write down a list of questions Detective Turner will need the answers to if he is to solve the crime. 

 

Task 3 Test your questions. 

It is very important that the questionnaire works, otherwise the murderer might get away.  Test your questions on some one to make sure that your questions gather the information you need. 

In your groups ask each other the questions that you have designed.  Write the answers to the questions on the worksheet. 

 

Task 4 Rewording questions

Watch the PowerPoint presentation Questions (Reso2b).  On Slide 3 you can see a list of the information Detective Turner needs to find out.  Go back to your list of questions.  It is very important that the questions are worded correctly and in a way that allows the correct information to be given. 

Look at your questions.  Have you asked the right questions?  Do you think that they will get you all the information you need?  Rewrite your questions and add any further questions if necessary. 

 

Task 5 Put questions into a questionnaire. 

Detective Turner would now like the questions put into a questionnaire.  Slide 4 and 5 show you how responses can be guided.  Remember that different types of questions can have different types of responses.  The question: What is your surname? could have millions of answers, whereas What colour hair do you have? will get a response that can be chosen from a list, as most people have one of four hair colours. 

Slide 6 and 7 show how questions can be written in a questionnaire format using a word processor or a spreadsheet.  Using MS Excel, create your questionnaire.  Save this into your Unit 7.5 Murder Most Horrid folder. Add your name to the header.  (View / Header and Footer / Custom Header).  Call it Murder Questionnaire.  You will need to use your worksheet Collecting Evidence. 

 

Task 6 Carry out a survey using your questionnaire. 

Detective Turner is very pleased with your finished questionnaire, and would now like you to interview three classmates. 

Print out three copies of your questionnaire to the KC2mono printer. 

Ask your classmates to fill them in. 

 

Extension Work

Annotate your questionnaires to explain why you have chosen to include each question and selected a particular method of response.  Remember it is good practice to use checkboxes and limit people to a few answers, because it makes it harder to give wrong information. 

 

 

7Y – Period 1: Wednesday 9th May

Starter: Access quiz – first 3 to 100% get stickers…

Task 1 – More queries

Detective turner needs to find out information about people’s whereabouts to support his case.

He needs to find out the following;

1. People who were in the English department
2. People who were in the Maths department
3. People who were in Reprographics

Open your suspects database from last lesson and make a query for each of the above (or do you know a more efficient way of doing this…) your queries need to include; ID, Surname, Forename, Hair colour, Shoe size, Distinguishing marks, Height, Alibi.

Task 2 – Creating reports

Queries are a great way to find information but they do not always display information very well.  Creating a report in Access gives you more formatting control (colours, fonts, layout etc.).

After you have been shown how to create a report, create a report for each of your queries.

Include all of the fields from your queries and the title of the report should be Alibi Report

Extension – Creating a logo

Reports allow you to add images.  Can you remember creating a logo for PEAT using Serif PagePlus? See if you can create a logo for Murder Most Horrid and then add this to the top of your report.  You will need to export your logo as a png file before you can add it to your report.

Task 3 – Printing reports

You need to print out your three reports before the end of the lesson.  Make sure you use the label tool to add your name to the footer on your report before printing.

If you finish…

Have a go at any of the exercises on this page: http://www.reviseict.co.uk/games/index.shtml?searchQuery=access

 

7W Period 3 Friday 18th May 2012

Starter: Yacapaca - please logon and take the 7.4 test.

Please click on this LINK and complete the SURVEY QUESTIONS.

7.5 Lesson 1 Murder Most Horrid – What have the police found so far. 

LEARNING OBJECTIVES

In today’s lesson you will learn how to;

• Learn how to get information from data

STARTER – Who dun’ it!

Look at your murder suspect card.  Now listen to my description of the murderer.  Remain standing if the feature I read out applies to you, if not then sit down.  I will keep reading out features until there is only one of you standing.  That person is the murderer!

Task 1 Setting up a New Folder. 

Within your ICT folder create a new folder called Unit 7.5 Murder Most Horrid.

INTRODUCTION

A body has been found in the Looneyville School Hall.  The police have been called in, and are attempting to interview all of the staff and students at the school.  This is a massive task and the police are struggling to keep track of the information.

As the school’s resident computer expert, you have been asked to help them design a way of storing all of the witness statements.  The police would also like to be able to search this information to help them eliminate people from their enquiries.

Task 2 Witness Details. 

So far, the police have interviewed the deputy head and head teacher.  They have collected descriptions of all of the teachers, and where they were at the time of the murder.

Open the spreadsheet Witness Details (Reso1c) LESSON 1.  Save this into your Unit 7.5 Murder Most Horrid folder.  Add your name to the header (View / Header and Footer / Custom Header).

The police have received a tip-off that someone with brown or black hair was seen leaving the dining hall at the time of the murder.

On your copy of the spreadsheet Witness Details highlight the following information:

• People who have black hair.
• People who have brown hair.

Search for information this way is time-consuming, and it is easy to make mistakes.

Can you think of a better method of storing this information?  HINT: card index / electronically.

The police will need to interview everyone in the school – over a thousand people.  They need a method of storing the data that will allow them to find information quickly.

Start thinking about…

What is a database? – A database is like a big library.  Instead of books, it stores pieces of information called records.  We can search a database to find information we are looking for.  Your school will have information about you on a database!  What sort of information do you think the school database has about you?

Task 3 Weed out wrong information!

The police are concerned that some of the information that people are giving is wrong.

Using another colour highlight any information on your copy of the spreadsheet Witness Details that you think could be wrong.

Work in your groups.  Complete the questions at the bottom of the sheet:

• What might happen if information that is given by witnesses is wrong?
• How could the police stop people from giving wrong information?

Task 4 Find specific information in a database. 

Open the database Murder Suspects (Reso1d) LESSON 1.  Now open the table Witnesses.

You are going to find all of the people with black hair.  Find the column containing hair colour and click in a cell in this column containing the word Black.  Then choose Records / Filter / Filter By Selection.  This will find all the people in the database with black hair.

Extension Work. 

Work in your groups.  Grab a white board, pen and cloth.  Jot down a list of information that the police might need to catch a murderer.

FINALLY…

If you have competed everything else, click on this LINK and try some of the MS ACCESS games.

7Y – Period 3: Wednesday 2nd May

Starter: Look at the words on the board – choose three that you would like to explain to the person sitting next to you and one that you are not sure about and you would like them to explain it to you.

Task 1 – Solving the crime

The police have created a database which contains details of potential suspects.

Navigate to this folder: S:\Pupils\Senior Pupils\ICT\Y07\Unit 7.5 Murder Most Horrid\Lesson_4 and right click and copy the file called Crime Database.

Now navigate to your Documents and paste the file in your 7.5 folder.  Open the file from your Documents.

Using the worksheet and your query skills from last lesson can you work out who the murderer is?

This video is in an older version of Access but it might help to remind you how to do queries if you’ve forgotten – http://www.teach-ict.net/software/access/queries/simplequery.htm remember, you need to click on create – query design to start your query.

Task 2 – Contacting witnesses

Now a murderer has been identified, the police need to telephone various witnesses to ask them to appear in court.  You need to create a query that will find people by surname and you should include the following fields: Surname, Forename, House number, Street, Town, Telephone number.  Test your query to see if you can find a witness with the surname “Hirst”.

Task 3 – Efficient –  ”Parameter” queries

Instead of having to change the Surname in the design view of your query each time you can use a more efficient parameter query.  Watch this video to find out how and then change your surname query to a parameter query and test it to make sure it works.

Extension

Can you create a parameter query than allows you to type in someone’s town, hair colour and shoe size each time you run the query?  The query should also inlcude their Forename and Surname.