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:
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.1 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.
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).
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.
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’)|
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
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.
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
5. Stand out
How about going the extra mile and:
- 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