G042 - Lecture 12

Using Local Databases

Mr C JohnstonICT Teacher

www.computechedu.co.uk

Session Objectives

Know how to search for information on a local database using both logical (Boolean) and relational (comparison) operators

Present evidence for task c of the unit by searching a local database for statistics which aid your investigation.

Terminology

Data Field A single piece of data e.g. Name.

Data Type Each field has a data type – could be text, number, date

Data Record A series of related fields make a record

Database Table A collection of data records

Search Criteria Characteristics used to find specific records

Logical (Boolean) Operator Used to create better search criteria – AND, OR, NOT

Relational (Comparison) Operator Used to create better search criteria - >, <, <>, *

Other criteria – [Parameter], Aggregation

Operators Explained

NOT – used when something not required AND – used when two or more criteria required OR - used one or another criteria is required > / >= - used to find records whose value is more than the

criteria < / <= - used to find records whose value is less then the criteria LIKE / * - wildcard to find records where criteria appears at some

point [enter XXXXXX] – parameter - allows user to enter their own

criteria

Access also contains some aggregation facilities - sum, average, max, min, count and the facility to do calculations with dates – differences, ages from date of births

Group Task

Work with somebody who is doing the same investigation as you and download from my website the correct database from my website.

Look at the tables within your database and think about some of the queries which you could do

Write the queries down as questions:

e.g. All people who live in hillfields over 20 and like comedy or action films

Think about how the question could be phrased in database landAREA = HILLFIELDS AND

AGE > 19 AND

FAV FILM TYPE = COMEDY OR ACTION

Using Operators

I want to find all customers whose favorite type of movies are action or comedy – have a customer rating over 5 and are not from Hillfields.

action or comedy

Over 5

Not Hillfields

Other facilities – Sorting Results

1. Select either ascending or descending from the sort row in the query design grid

2. View Results

Other facilities – Summarising Results

1. Hit the “Totals Icon” when in query design view

2. Add the field you want to summarise again and select count from the Total row

3. View Results

Task C

Band 1 Requires you to use logical or relational operators in

local database searches, Band 2

Requires to use a range of both logical and relational operators in local database searches,

Band 3 Requires to use a range of both logical and relational

operators in local database searched and produce printed reports clearly showing the results.

Task Best Practice

1. Download the local database which relates to your investigation

Investigation1 – relocate to Devon Investigation2 – find a university course / location Investigation3 – uk themepark thoughts

2. Look at the data in the table and write down a list of questions utilising operators which will help you answer your investigation

3. Make queries and show evidence – don’t need step by step

4. Make reports – ensuring they are edited so titles and data field name make sense before printing.

Topic Exercise

Create evidence which meets the needs of task C.

Remember to print it out and place in your folder as you go along to avoid any printer credit issues.

Useful Sources

Investigation1 Database Investigation2 Database Investigation3 Database Operator Help Sheet Making Reports In Access Resources – Available

OnLine