Database banner image.

Previous step: Phase 2 Excel Charts


The Grade 11 & Grade 12 PAT requires you to create a database using Microsoft Access. You have already captured your questionnaire data in Excel. Rather than re-type the data, we will simply use Access’ handy import feature. Please read the post on Access’ Import Excel spreadsheet function to better understand how the names of your Excel columns are treated by Access.

It is vitally important to note that the requirements around the data collected have changed this year (2021) for the Grade 12 PAT. The requirements are more specific, especially regarding the “extra data from a source other than your questionnaire” of previous years. You will be expected to collect data from a separate source and compare the information derived from your questionnaire to this extra data source – this will require a bit of extra planning to ensure that you can find data “external” data to match with your questionnaire data.

In this post:

  1. Tables
    1. Questionnaire data
    2. Other data
  2. Queries
  3. Forms
  4. Report
  5. Stand out

You could create a lookup table for the suburb, employment status or gender field.

As of 2021, you must have a database with at least 2 tables. Create a new, blank Microsoft Access Database. Remember to name your database file meaningfully!

1. Tables

1.1 Table for questionnaire data

  1. Select the External Data menu tab
  2. Left-click on the Import Excel Spreadsheet button
  3. Browse to your Phase 2 Excel workbook
  4. The default option Import the source data into a new table in the current database is the correct option
  5. Left-click on OK.

Top Tip: Validation rules and Masks only apply to new records. So you must test your tables by adding new records to see if they are working correctly (you can always delete the records later).

Back your data up by making copies of your tables at regular intervals.

Now that you have your Raw data in a table, make a copy of that table and name it tblReponses.

Open tblResponses in Design View and edit the properties of the various fields as appropriate. The Learner Guide specifies:

  • All fields must have an appropriate, meaningful Name (names do not contain spaces or special characters such as punctuation).
  • All fields must have appropriate Descriptions: for the fields containing answer to questions, copy the full question itself into the Description and rename the field to something brief, like Question1
  • All fields contain single data units (e.g. separate fields for Name and Surname)
  • All Short Text fields must be set to an appropriate Size (do not leave any Short Text fields with the default size of 255).
  • There must be at least one appropriate/meaningful Validation rule with suitable Validation text.
  • There must be at least one appropriate/meaningful list/combo box.
  • There must be at least one appropriate/meaningful Input Mask.

Use the Number data type for data in the form of numbers that you can perform calculations on, for example, the Age column on which you can calculate the average age of the respondents etc.

Create Lookups for fields such as Gender, Suburb and Race, as well as for closed questions.

1.2 Table for data from other source

Create this table from scratch — in other words, do not create it by using Access’ data import function.

2. Queries

Grade 12

You need to create a set of Queries. Name your Queries (in fact all your database objects) meaningfully and in such a way that it is possible to understand what the result of the processing will be, e.g. qryCountOfFemalesPreferringChocolate

LEVELCOMPLEXITY OF QUERY
1Only fields with 1 simple criterion (e.g. ‘X’, >X, =X, not ‘X’, Is Null)
2One field with combined criteria using conditions and relational operators (e.g. ‘X’ or ‘Y’, >1 and <10 or ranges such as ‘Between 1 and 10’)
OR
One field with a criterion including wild cards (‘*’ or ‘?’)
3Simple calculated field using arithmetic operators ( +, – , * , / )
OR
Date and time functions/calculations
4Complex calculated field (e.g. SUM, MIN, MAX, AVG, COUNT)
OR
Queries that use Grouping
OR
Queries that use logical (IF) functions or text functions
OR
Advanced functions that are not in the CAT CAPS curriculum
Figure 14: Levels of the complexity of database queries required (from PAT Learner Guide 2021)

If one of your closed questions allowed the respondent to select more than one option you have the perfect opportunity to use the Wildcard as part of the criteria in your queries (Level 2).

If one of your questions required a rating from 1 to 10, you have the perfect opportunity to use range expressions in your queries (Level 2).

Adding criteria to two fields and at the same time as using the Totals button to Group (aggregate) your data, will give you a Level 4 query.

Add an UPDATE Query to “clean” your data using Access’ Trim function, or to split multiple values in one field into two fields.

3. Forms

There are no requirements for Forms, however, I highly recommend creating at least one basic form. You can also use a Form to add a Splash Screen for your database.

4. Report

You must create at least one report. The Learner Guide specifies that:

Your report must:

5. Stand out

How about going the extra mile and:


Next step: Phase 3 Final Report

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.