In this section, you will learn how to store and manipulate data using database software.


Section 11: Data Handling 1 – Databases and Charts

Candidates should be able to use database and charting facilities to store, search and manipulate data, solveproblems and to represent data graphically.Using database facilities, candidates should be able to:
11Syll1.png
11syll2.png

Files you will need for this section:



blue-b.gif

Lesson 1: Importing & interrogating data

Objectives:

  • Know how to import a csv file into a new database
  • Understand the need to be careful when choosing field names & data types
  • Be able to import a data file, create queries to interrogate the database
  • Be able to create reports based on your quesries
  • Be able to export data from a query

What you need to do:

  1. Open and save this file as a text file
  2. Open Microsoft Excel.
  3. Choose File / Open......
  4. Navigate to the folder containing DodgyDave1.txt. Choose Files of type : All files
  5. Select DodgyDave1.txt
  6. Select Delimited by comma (see below)
  7. Inspect the data and then save as a comma separated values file (see below)
  8. Open the powerpoint (right) and follow the instructions.

Delimit.png
delimit2.png
saveascsv.png
DodgyDave1.png
Click the thumbnail (above) for your tasks.


The text file for Step 1:


When you have saved it as a .csv it should look like this:


After importing into a database, it should look like this:

blue-b.gif

Lesson 2: Importing & interrogating data

Objectives:

  • Be able to hide data in reports
  • Be able to create labels
  • Be able to use formulae in Queries
  • Be able to use formulae in reports
DodgyDave2.png
Click on the thumbnail above for the powerpoint.


The labels in this database show you some of the things you can do:

blue-b.gif

Lesson 3: Using wildcard operators

In this lesson we will consolidate the following concepts & skills:
  • Field names/data types: making them meaningful and appropriate
  • Importing data: getting it right
  • Complex queries: using wildcard searches and operators
  • Exporting data : to Excel, Word and Notepad
The file you need is Stationery.csv.

All your instructions are in the presentation (right) along with screenshots to help you.
ABC_L1Intro.png
Click on the thumbnail for the powerpoint.




Right-click and save this image for use on the labels you will create today:
Sale.png
blue-b.gif

Lesson 4: Grouping, and using calculations in queries and reports

In this lesson we will consolidate the following concepts & skills:
  • Grouping
  • Creating summaries
  • Using calculations in queries
  • Using calculations in reports
The file you need is Stationery.csv
All your instructions are in the presentation (right) along with screenshots to help you.
ABC_L2Intro.png
Click on the thumbnail for the powerpoint.