This document instructs how to code questionnaire outcomes and how to enter the data from completed questionnaires. The Excel ‘Data entry template’ document provided with this Toolkit should be used to enter survey responses.
NB: not all columns in the spreadsheet will apply to your organisation (e.g. ICB code). You may leave these columns blank.
The Unique Record Number (URN)* was assigned to every record at the sampling stage. When a questionnaire is returned or someone opts out, the URN number must be sought (i.e. from the front page of the questionnaire) and the ‘Outcome Code (survey status)’ column (column Q) must be updated immediately to show the latest status.
*This number should be a combination of a 3-character code that is identical for every record (you can use a survey code such as ‘COM’ or if you are an NHS Trust you can use your three-character Department of Health code), followed by a series of 4-digit numbers ascending from 1001 assigned by you e.g. ABC1001. The final 4-character number should be unique for each record.
For confidentiality reasons, survey responses must never be matched to the complainants that made them. The best way to ensure this is to store name and address details separately from survey response data.
Where there are likely to be fewer than 10 respondents to the survey, please note that the generalisability of the findings will be limited and should be treated with caution. Small numbers of respondents will also increase the likelihood that respondents can be identified, particularly where results are broken down by demographic criteria.
Outcome code
When a questionnaire is returned (blank or completed), the date and URN should be captured in the entry template, together with the survey status as listed below:
| Code | Survey status | Action |
|---|---|---|
| 1 | returned a completed questionnaire | Enter survey data in the ‘Data entry template’ and remove from further mailings |
| 2 | survey returned undelivered i.e. wrong address | remove from further mailings |
| 3 | deceased | remove from further mailings |
| 4 | opted out of survey e.g. via returning a blank questionnaire or opting out over the phone | remove from further mailings |
| 5 | not eligible to take part (e.g. haven’t made a complaint) | remove from further mailings |
This initial information is needed to know which records need sending reminder letters and on the other hand, records who do not want to receive another letter, e.g. who want to opt out of the survey. Please note that at no point the questionnaire can be separated from its URN page. When a survey is returned completed (code 1) the data should be entered as outlined below.
Questions: general Information
The data should be entered in the ‘Data entry template’ document provided in this Complaints survey Ttoolkit.
For all questions (unless specified to tick all that apply), capture one marked box per question.
Exceptions are:
| If multiple boxes are ticked for a single response question: | Response should be left blank |
| If a response is missing: | Response should be left blank |
| Respondent crossed out a response: | This response should not be captured |
| Responded crossed out a response and marked a second response: | The second choice should be captured if it is clearly seen |
| Any doubts: | Leave the response blank |
Questions: response codes
| Response Codes: | Small number next to the box marked to be entered as response (this does not apply to ‘tick all that apply’ questions |
Some questions have instructions to the respondent to skip following questions, ignore this instruction and key all responses as seen.
Questions: tick all that apply
For any ‘tick all that apply’ questions, please key in each response a ‘1’ in the relevant field. For any fields with no response selected, please leave the field blank.
For the short questionnaire this applies to question:
- Q20
For the standard questionnaire this applies to questions:
- Q24
- Q45
For multi response questions each response option will have its own column in the ‘Data entry template’ and for example response option 3 for question 20 (on the short questionnaire) will be named Q20: 3. If this response option was selected (together with response option 1 and 2), you would add a ‘1’ in column Q20:1, Q20:2 and Q20:3.
Example:
- Q20:1= 1
- Q20:2= 1
- Q20:3= 1
- Q20:4=
- Q20:5=
- Q20:6=
- Q20:7=
- Q20:8=
No responses selected for Q20:4 – Q20:8 Q20:2= 1
Questions: free text requirements
| Singular responses (no, yes, no comments, N/A, etc.) | Should not be captured |
| Sentence case | All free text to be written in sentence case |
| Illegible text | Do not guess but replace with asterisks |
| Spell check | All free text must be entered verbatim, do not correct spelling. |
| Date | All free text regarding this must be entered verbatim |
| Job descriptions | All free text regarding this must be entered verbatim |
| Illnesses | All free text regarding this must be entered verbatim |
Free text comments may highlight safeguarding issues. Your organisation should have a process to ensure these are reviewed and acted on appropriately.
Free text comments may include personal and sensitive information. Your organisation should consider whether any redaction needs to take place if these comments are shared more widely, such as removing names or censoring swear words.
Data checks
Before checking the survey routing and deleting the ineligible answers, please make some initial checks on the data.
Please make a copy of your original data file which can be named ‘Final Data- MASTER’ and the copy can be named ‘Final Data- Checked’. Making this copy is very important, as if anything goes wrong with checking the data, the original file is still intact.
| Checking area | Instruction | Complete? | |
|---|---|---|---|
| Questions | Check if all questions are included in the file and are in the correct order | ||
| Number of responses | Check if all records that you expect to be there are there | Click [CTRL], [Shift], [Arrow Down], to select all the records and look at the total number appearing at the left hand bottom. | |
| Response Options | Look at all questions and check if all response options that you expect to be chosen are chosen at least once | Use filter arrow to select every question (every column) and check if most likely answers are included | |
| Look at all questions and check if there are no invalid response options e.g. option 5 on Q1 of the short version (has only 4 response options) | Use filter arrow to select every question (every column) and check if no ineligible response option occur |
Survey routing
After the initial checks, the data must be cleaned according to the questionnaire routing instructions. Routing ensures that the respondent only answers questions relevant to them. For example, question 4 in the survey instructs respondents that select the first option to skip to question 6.
You therefore need to make sure that all those selecting the first response option for this question did not answer question 5, and if they did then their answers need to be removed since the question was not applicable to or should not have been answered by them.
Cleaning the data can be done in Microsoft Excel using the filter option to select which responses (should have been left blank and thus) need deleting – by following the table below. Before you start cleaning the file, make another copy of the ‘Final Data-Checked’ file which can be named ‘Final Data-Cleaned’. Making this copy is very important, as if anything goes wrong with cleaning the data, the original file is still intact.
Please print the table below and on the next page and use the ‘complete’ column to tick once it has been actioned.
Short version of the questionnaire:
| Q | Data cleaning rule | Instruction | Complete? |
|---|---|---|---|
| 13 | If answer to Question 12= 3 or 4, delete any answers to Question 13 | Use filter arrow to select only those that answered ‘3’ and ‘4’ to question 12. For these records, delete any responses they have given to questions 14 |
Long version of the questionnaire:
| Q | Data cleaning rule | Instruction | Complete? |
|---|---|---|---|
| 3 | If answer to Question 3 = 1, delete any answers to Question 4 | Use filter arrow to select only those that answered ‘1’ to question 3. For these records, delete any responses they have given to question 4 | |
| 8 | If answer to Question 8 = 1 or 2, delete any answers to Question 9 | Use filter arrow to select only those that answered ‘1’ and ‘2’ to question 8. For these records, delete any responses they have given to question 9 | |
| 12 | If answer to Question 12 = 2, 3, or 4, delete any answers to Question 13 | Use filter arrow to select only those that answered ‘2’, ‘3’ and ‘4’ to question 12. For these records, delete any responses they have given to question 13 | |
| 16 | If answer to Question 16 = 2, delete any answers to Question Q17 and Q18 | Use filter arrow to select only those that answered ‘2’ to question 16. For these records, delete any responses they have given to question 17 and 18 | |
| 17 | If answer to Question 17 = 1 or 2, delete any answers to Question Q18 | Use filter arrow to select only those that answered ‘1’ and ‘2’ to question 17. For these records, delete any responses they have given to question 18 | |
| 27 | If answer to Question 27 = 1, delete any answers to Question Q28 | Use filter arrow to select only those that answered ‘1’, to question 27. For these records, delete any responses they have given to question 28 | |
| 32 | If answer to Question 32 = 1, delete any answers to Question Q33 | Use filter arrow to select only those that answered ‘1’, to question 32. For these records, delete any responses they have given to question 33 | |
| 34 | If answer to Question 34 = 3 or 4, delete any answers to Question Q35 | Use filter arrow to select only those that answered ‘3’ and ‘4’, to question 34. For these records, delete any responses they have given to question 35 | |
| 36 | If answer to Question 36 = 1 or 2, delete any answers to Question Q37 | Use filter arrow to select only those that answered ‘1’ and ‘2’, to question 36. For these records, delete any responses they have given to question 37 |