« 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. Create
Create a new, blank Microsoft Access Database. Remember to name your database file meaningfully!
You could create a lookup table for the suburb, employment status or gender field.
2. Tables
As of 2021, you must have at least 2 tables in your PAT database. Aim at having one table for your questionnaire data, a second table for your additional data and a third “lookup” table linked to either of the first two tables.
2.1 Table for questionnaire data
Import your questionnaire data from your Excel workbook:
- Select the External Data menu tab
- Left-click on the Import Excel Spreadsheet button
- Browse to your Phase 2 Excel workbook
- The default option to Import the source data into a new table in the current database is the correct option
- Left-click the OK button.
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 answers 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.
2.2 Table for additional data
Create this table from scratch — in other words, do not create it using Access’ data import function.
3. 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
| LEVEL | COMPLEXITY OF QUERY |
| 1 | Only fields with 1 simple criterion (e.g. ‘X’, >X, =X, not ‘X’, Is Null) |
| 2 | One 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 ‘?’) |
| 3 | Simple calculated field using arithmetic operators ( +, – , * , / ) OR Date and time functions/calculations |
| 4 | Complex 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 |
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 requires 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 as well as using the Totals button to Group (aggregate) your data in one query 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.
4. 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.
5. Report
You must create at least one Access Report. The Learner Guide specifies that your report must:
- Be sorted according to at least one field
- Be grouped appropriately on at least one field
- Contain at least one meaningful calculation using a function in the report footer/group section
6. Stand out
How about going the extra mile and:
- add a calculated field type to your table
- create an UPDATE query to clean your data,
- add a “Splash Screen” form as a showstopper for your PAT, or
- create and add an Application icon to your database?
Next step: Phase 3 Final Report »