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

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?

 

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

 

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.

7Y – Period 1: Wednesday 25th April

Starter: Access Fling The Teacher - first three flingers get stickers

Task 1 – Creating queries in MS Access

Open the file called SHR Pupils 2011-2012 which you saved in your 7.5 folder last lesson

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 2 – 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.

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.

7Y – Period 1: Wednesday 21st March

Starter: Please click here to take a survey – click on “Respond to this Survey”

New Unit – Please create a folder called Unit 7.5 – Murder Most Horrid in your ICT folder.  Now have a listen to your task below.

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 1 – Witness details

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

Police have had a tip off that someone with brown or black hair was seen leaving the dining hall at the time of the murder.  Using the Witness Details Sheet;

1. highlight all the people who have black hair
2. highlight all the people who have brown hair
3. Now answer the questions on the bottom of the sheet

Then a quick quiz

Task 2 – Creating queries in MS Access

1. Navigate to Shared:\Pupils\Senior Pupils\ICT\Y07\Unit 7.5 Murder Most Horrid\Lesson_1 and open 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)

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?

7Y – Period 1: Wednesday 7th March

Starter: Please grab your headphones and watch this video: http://www.techsmith.com/tutorial-jing-record-video.html

Task 1: Recording a tour of your Sid’s Snow School spreadsheet

Using Jing, record a video tour of your spreadsheet.  In your tour make sure you show and talk about the following;

1. The formatting (colours, fonts, borders etc.) you have used and why you have used them.
2. The formulas and functions you have used and how they work

When you are happy with your tour, change the name to “my spreadsheet tour by …..” and save it in your 7.4 folder.

Task 2: Uploading your spreadsheet tour to Voicethread

Log in to Voicethread.  Remember, your login is your school email address and your password should be your school password.  When you have logged in, follow these steps;

1. Click on My Voice.
2. Find the voicethread called Spreadsheet Tours by 7Y and click on the Menu button in the bottom right hand corner – then click edit.
3. Now click on upload and upload your spreadsheet tour, remember to put your name where it says (add a title and link).

Once you have uploaded your spreadsheet tour leave yourselves 2 stars and a wish – two great things about your work and one thing that could be improved.  You can also now view each other’s tours.  Please give each other feedback using 2 stars and a wish.

7Y – Period 3: Wednesday 29th February

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

Task 1: Excel test

Please open the file called Sid and Suki’s Skate Park which can be found in: Shared:\Pupils\Senior Pupils\ICT\Y07\Unit 7.4 Extreme Sports\Lesson_7

I would like you to complete the spreadsheet and answer the questions to the best of your ability.  You need to do this on your own without talking to anybody else.

Please save your spreadsheet in: Shared:\General\ICT\year 7\7Y  2011-2012\Sid and Suki’s skate park with your name in the filename.

If you finish…

Please have a go at any of the activities on this page;

http://www.reviseict.co.uk/games/index.shtml?searchQuery=excel

7Y – Period 1: Wednesday 22nd February

Starter: Wordwall Quiz

Please open Sid’s Snow School model which you saved into your documents last lesson

Task 1 – Adding extra costs to the spreadsheet

You may have completed this last lesson – check to see that you have the costs below and also check that your formulae are correct.

Sid has just realised that he has left out some costs for the ski school and these need to be added in.  These are;

• Rent of Premises: £750
• Electricity: £150

To add these variables to the model you firstly need to add in some extra rows.

Task 2 – Sid’s new products

Sid has decided to sell hot drinks, ski gloves and hats.

Add these products to both the revenue and costs section of your model by adding new rows.

Cost details from Sid’s suppliers are below;

• Hot drinks: Cost – £0.20, Sid is buying 250
• Ski gloves: Cost – £7.00, Sid is buying 35
• Hats: Cost – £5.00, Sid is buying 42

Add this information to your costs.

Task 3 – Making a profit

Sid will need to sell his products for more than he bought them for.

• He has decided to sell his hot drinks for £1.50 and he will sell all of them.  Add this to the model and calculate the revenue from the hot drinks.

He wants to sell the Ski Gloves and Hats for double the price he has paid for them.  This is 200% of the cost and is called the “Mark Up”.  Again, he thinks he will sell all the Gloves and Hats.

• Calculate the price that Sid will sell the Gloves and Hats for and then work out the revenue.

Extension

Enter 200% in a separate cell on your spreadsheet and label it as Mark Up in the cell above or to the left.

Now, using absolute cell references ($A$1 etc.) can you create a formula that uses the Mark Up to calculate the prices Sid will charge for his hats and gloves?  This might help…

If it works you should be able to easily change the Mark Up percentage and see the effect on profit.

Pick a new Mark Up and see if you can guess what the new profit figure will be – make the change and see how close your guess was.

Challenge a friend to see if they can do better than you!

7Y – Period 3: Wednesday 8th February

Starter: Please complete the rules and variables sheet

Task 1 – Silent recap

Task 2 – Sid’s ski shop

Sid runs a smashing snow school in Scotland.  He has put together a spreadsheet model to help him work out his revenue, costs and profit but it needs some changes making to it and this is where you come in.

Your first task is to add formulae to the spreadsheet so it calculates Sid’s profit correctly.  The spreadsheet can be found in: Pupils:\Senior Pupils\ICT\Y07\Unit 7.4 Extreme Sports\Lesson_4 and it is called Sid’s snow school – Res4c

Task 3 – Formatting the spreadsheet

Currently the spreadsheet isn’t formatted particularly well.  Your second job is to format the spreadsheet so that cells containing money have a £ sign and two decimal places (currency format).  After that, you need to use font styles, colours, background shading and borders to improve the presentation of the spreadsheet.

If you are stuck on how to format cells to currency follow this link. 

Extension: If you finish quickly, why not add one or two relevant images and maybe some links to any Scottish ski resorts you can find on the internet?

Task 4 – Adding variables to the spreadsheet

Sid has just realised that he has left out some costs for the ski school and these need to be added in.  These are;

• Rent of Premises: £750
• Electricity: £150

To add these variables to the model you firstly need to add in some extra rows.  Click here to watch a video about how to do this. (this shows you how to insert a column but the same principle works for adding rows)

After you have added your new costs, check the formulae in your model.  Do they still add up the correct cells?  If not, change them so that they are correct.

Extension: Visit some ski schools on the internet and make a list of goods or services that Sid could offer to increase his revenue.