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.
As of 2021 you must have a database with 2 tables. Create a new, blank Microsoft Access Database. Remember to name your database file meaningfully!
Table for questionnaire data
- Select the External Data menu tab
- Left-click on the Import Excel Spreadsheet button
- Browse to your Phase 2 Excel workbook
- The default option Import the source data into a new table in the current database is the correct option
- Left-click on OK.
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 appropriate, meaningful names.
- All fields contain single data units (e.g. separate fields for Name and Surname)
- All text fields must be set to appropriate sizes.
- There must be at least one appropriate/meaningful validation rule and validation text.
- There must be at least one appropriate/meaningful list/combo box.
- There must be at least one appropriate/meaningful input mask.
Table for data from other source
Create this table from scratch — in other words do not create it by using Access’ data import function.
You need to create a set of Queries.
|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’)|
One field with a criterion including wild cards (‘*’ or ‘?’)
|3||Simple calculated field using arithmetic operators ( +, – , * , / )|
Date and time functions/calculations
|4||Complex calculated field (e.g. SUM, MIN, MAX, AVG, COUNT)|
Queries that use Grouping
Queries that use logical (IF) functions or text functions
Advanced functions that are not in the CAT CAPS curriculum
You must create at least one 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
How about going the extra mile and:
- adding a “Splash Screen” form as a showstopper for your PAT, or
- creating and adding an Application icon to your database?