You must submit two copies of the assignment as follows:
- ONE electronic copy submitted via Moodle.
- ONE hard copy to be submitted at the Management Reception.
The timely submission of assignments is your responsibility, and excuses — such as finding a long queue in the Computer Centre to print out — will not be accepted. You will receive an electronic submission receipt ID via email. It is your responsibility to keep this safe as proof of submission. You are also strongly recommended to keep a copy of all submitted assignments.
Your school district wants to evaluate teachers based on their students’ scores on standardized tests. In this case, you will use Microsoft Access and Microsoft Excel to create the evaluation system.
Your state’s education officials complain that virtually all the teachers in your school district are rated as good or excellent by their school principals. There are surely bad teachers, state officials say, but they are not identified and the problem is not addressed.
Therefore, the public school district has been instructed by state education officials to devise an accountability system to identify good teachers and bad teachers. Good teachers would be paid more. Bad teachers would be retrained so that they improve, or they would be fired. Fired teachers then would be replaced by good teachers. In this way, officials believe that teaching would improve and students would learn more.
In the new system, subjective evaluations by school principals will not be the only measure of teacher success. In addition to principal evaluations, an objective method is needed to measure teacher performance. Your state’s standardized tests are considered a reliable measure of student learning, which means that the test scores could be used to help measure teacher performance objectively. According to some state officials, teachers whose students do well on the tests are doing a good job; poor test scores must be a sign of bad teaching. The concept of matching teacher performance with student performance is highly controversial.
In your state, public school begins with kindergarten. Starting in first grade, elementary students take a standardized test at the end of each school year. The test has a reading section and a math section; the summary score ranges from 0 to 1000. These standardized tests are given for grades 1 through 8; the test becomes more difficult and comprehensive each school year.
Your district superintendent thinks that parental involvement is a key factor in a student’s motivation and learning. The superintendent wants parents to supervise their children’s homework, meet regularly with teachers, and be involved with the school. The superintendent thinks that children of “involved” parents usually do better in school. In your district, K-8 teachers are required to note the extent of parental involvement, and to report this information as a rating to the principal and the superintendent. Teachers are required to document these ratings by recording missed meetings, missed homework assignments, and so on. District officials think the ratings are reliable because they are documented and are rarely debated by parents.
You have been asked to develop an accountability system that incorporates standardized test scores and parental involvement ratings. Your model will be a prototype. The model will use test score data for all second-grade students in the district. To develop a sample group for comparison, you have identified three second-grade classes in a district school. You have two years of data for students in these classes.
An Access database file named TeacherEval.accdb contains your data. Use Windows Explorer to copy the TeacherEval.accdb database, which is in the MN1505 subfolder within the Management folder on the pclabs (\\ourdata\teaching) (R:) drive, to your Y: drive. [If your Y: drive is full you may need to delete some of its contents to be able to do this].
The tables in the file are discussed next. Figure 1 shows the first few records of the Year1Students table.
Figure 1 Year1Students table records
The table shows each student’s ID number and indicates whether parents are involved in the student’s education. Each second-grader in the district is assigned a unique student number. A lack of parental involvement is indicated by the text value “YES” in the LackOflnvolvement? field. If the parents are involved, the entry is “NO.”
The district had 548 second-grade students in Year 1 of your model. A companion table called Year2Students holds data for second-grade students in Year 2 of your model. The two tables contain two different sets of students. By sheer coincidence, Years 1 and 2 had the same number of second-grade students, 548.
Your model will also include two years of student records for three second-grade teachers in a particular district school. The teachers are named Smith, Jones, and Casey. Figure 2 shows the first few records of the Year1ClassAssignments table.
Figure 2 Year1ClassAssignments table records
The three teachers each had 23 students in their second-grade class each year. Each teacher’s class has a unique number. Here, teacher Smith’s Year 1 class was number 1. The table shows how individual students were assigned to each class. Each student is given a unique number, which is the table’s key field.
Figure 2 shows that teacher Smith had students 1, 2, and 10 among the 23 assigned to her. Teacher Casey’s class of 23 included student 5.
In your district, most students attend the school in their neighbourhood. Occasionally, parents request that their child attend a school other than their neighbourhood school, but such requests are not always honoured. Within a school, the principal assigns students to a teacher; the teachers are not allowed to request particular students.
A companion table called Year2ClassAssignments shows how students in Year 2 were assigned to the same second-grade classes used for Year 1. No students were “kept back” at the end of Year 1, so no Year 1 students reappear in a teacher’s class in Year 2.
Each second-grade student in the district took standardized tests at the end of first grade, and again at the end of second grade. Scores for Year 1 students are summarized in the table Year1StudentTestScores.
The first few records of the table are shown in Figure 3.
Figure 3 Year1StudentTestScores table records
Student l’s scores on the two year-end tests were 453 and 456. Each year’s test emphasizes maths and reading, and each succeeding test is more comprehensive and more difficult. Thus, Student 1 did not appear to progress greatly in second grade. Student 2’s second-grade score appears to represent a decline in ability. Student 3’s second-grade test score shows improvement over Year I , although the scores are not high in either year.
The YearlStudentTestScores table has 548 records, one for each Year 1 student. A companion table, Year2StudentTestScores, contains records in the same format for second-grade students in Year 2 of the model.
Your model will use two criteria for assessing teacher performance.
- Criterion 1: The average test score is computed for a second-grade class taught by one of the sample group teachers. The average test score is also computed for all second-grade students in the district. If the sample teacher’s class average is higher than the district average, the teacher is rated “good” for the year; otherwise, the teacher receives a “poor” rating for the year.
- Criterion 2: Each student has a first-grade test score and a second-grade test score. The average test score improvement is computed for the sample teacher’s class and for all second-grade students in the district. If the teacher’s class has a better average improvement than the district’s, the teacher is rated “good” for the year; otherwise, the teacher receives a “poor” rating for the year. For example, if a teacher’s class had an average first-grade test score of 460 and an average second-grade test score of 474, the average improvement of the class is 3% (1.03 * 460 = 474). If the average district student showed a 2.5% improvement from the first-grade test to the second-grade test, the teacher would receive a “good” rating for the year.
You want to see if the two criteria produce the same ratings for a teacher in a year and if a criterion produces the same rating for a teacher two years in a row.
TASK 1: MAKING QUERIES IN ACCESS
In this task, you will design and run four queries in Access.
Year 1 Test Scores for All District Students
Create a query whose output shows Year 1 test scores for all second-grade students in the district. Your output should look like that in Figure 4. The query should generate 548 output records, although only the first few are shown. Name the query Year1ScoresForDistrict.
Figure 4 Year 1 scores for all second-grade students in the district
You should also create a query for Year 2 test scores of all second-grade students in the district. The output format is the same.
Year 1 Test Scores for Students in Sample School
Create a query whose output shows Year 1 test scores for all second-grade students in the sample school. The query should generate 69 records for the three teachers in your model. Your output should look like that in Figure 5; only the first few output records are shown. Name the query Year1TestScoresForSchool.
Figure 5 Year 1 scores for second-grade students in sample school
You should also create a query for Year 2 test scores of all second-grade students in the sample school. The output format is the same.
When you finish the queries, save and close the TeacherEval.accdb file.
TASK 2: USING EXCEL FOR DECISION SUPPORT
In this task, you will import the output of the four Access queries into Excel worksheets and then develop information needed to rank the three teachers.
Importing Query Data
Open a new file in Excel and save it as TeacherEval.xlsx.
Import the YearlScoresForDistrict query output into Excel. Click the Data tab and then click From Access in the Get External Data group. Specify the Access filename, the query name, and where to place the data in Excel (cell A1 is recommended).
The data will be imported into Excel as an Excel data table, which is the format you want. If cell A1 is not already selected, click it. In the Table Style Options group, select Total Row to add a Totals row to the bottom of the table. Rename the worksheet YearlScoresForDistrict. The first few rows of your worksheet should look like Figure 6.
Figure 6 Rows in the Year1ScoresForDistrict worksheet
Import the Year2ScoresForDistrict query output into another worksheet. Add a Totals row and name the worksheet Year2ScoresForDistrict.
Import the YearlTestScoresForSchool query output into a third worksheet. Add a Totals row and name the worksheet YearlScoresForSchool. The first few rows of your worksheet should look like Figure 7.
Figure 7 Rows in the Year1ScoresForSchool worksheet
Finally, import the Year2TestScoresForSchool query output into a fourth worksheet. Add a Totals row and name the worksheet Year2ScoresForSchool.
Using Data Tables to Gather Data
You will use the data tables to gather data needed to evaluate teacher performance for the two years. You could also use pivot tables to gather the data; your instructor may require them instead. However, data tables are probably more convenient in this case.
Before gathering data, you need a worksheet in which to manually enter data that you develop. Create a new worksheet named Summary. You can use any format that lets you compare one value to another. For example, the format shown in Figure 8 is acceptable.
Figure 8 Possible summary worksheet format
Most data for the summary worksheet will come from the data tables. Data in the Improvement column would be determined by an Excel formula in which one cell value is divided by another. The Criteria cells should include an explanatory comment; to enter one, right-click in the cell and click Insert Comment. The bottom section, “Lacks Involvement-Yes”, records counts of students whose parents lacked involvement in each teacher’s classes during the two years. For example, Smith might have had 8 uninvolved parents in Year 1 and 12 in Year 2.
You should now gather data for the sample teachers’ classes and for the district, using the four data tables. As data is gathered, enter the values manually into your summary sheet. Keep the following points in mind:
- Click the arrow in the Totals row to select summary values for a column’s data, such as average, or counts.
- Column headings also have arrows. You can stratify data in a column by clicking a value on or off. For example, suppose that your worksheet showed people’s heights and gender. You could click to hide information for males, leaving just female data. Then you could use the Totals row to determine the average height for females. To restore the male data, you would click the heading arrow and then click Select All.
Fill in the Criteria cells after inspecting the data. Then consider the following eight questions:
- What is the impact of parental involvement? Is it true that children of involved parents do better academically in this school district?
- Using Criterion 1, are any teachers rated as good in one year but poor in the other? If so, does seem reasonable that a teacher’s ability changes from year to year? How can these rating changes be explained?
- Using Criterion 1, does it appear that any teachers are classified incorrectly? In other words, are any teachers considered good when they actually are poor, or vice versa?
- Is Criterion 1 a reasonable way to assess teacher ability? Why or why not?
- Using Criterion 2, are any teachers rated as good in one year but poor in the other? If so, does seem reasonable that a teacher’s ability changes from year to year? How can these rating changes be explained?
- Using Criterion 2, does it appear that any teachers are classified incorrectly? In other words, are any teachers considered good when they actually are poor, or vice versa?
- Is Criterion 2 a reasonable way to assess teacher ability? Why or why not?
- Based on the data, how would you describe the abilities of each teacher?
TASK 3: DOCUMENTING FINDINGS IN A MEMORANDUM
In this task, you write a memorandum in Microsoft Word that documents your findings. Your memo needs tables that show teacher ratings by the two criteria for each year. The format of the tables is shown in Figure 9; you will also need a table for Year 2.
||Year1 Rating by Criterion 1
||Year 1 Rating by Criterion 2
Figure 9 Format of table to include in memo
In your memo, observe the following requirements:
- Your memo should have proper headings, such as Date, To, From, and Subject. You can address the memo to the district superintendent.
- Briefly outline the situation. However, you need not provide much background — you can assume that readers are generally familiar with your task — but you need to show that you have understood the situation and what is required of you.
- In the body of the memo, briefly describe the model you created. Be sure to state the two accountability criteria. List the ratings given to the three teachers, and refer to the tables that summarize the ratings.
- List the answers to the eight questions using appropriate data to support them. Then state your opinion of measuring teacher accountability by standardized test scores. If the method has problems, how do you think the problems can be minimized?
- The memorandum should be between 1000 and 1500 words in length.
The submitted and assessed part of this coursework is a business-style memorandum, rather than an academic essay. Thus, the marking criteria are different from those usually required for an academic essay. Your assignment will be assessed on the following criteria:
- Fulfilling the requirements of the brief
- Use of data/charts to support statements made
- Writing style
- Quality of presentation
I do not expect you to use in-text references nor to provide a bibliography/reference list at the end of the memorandum.
TO BE SUBMITTED
- Your memorandum (printed copy and electronic submission via Moodle). It is this memorandum on which your mark will be based.
- CD or USB stick which should contain your Excel spreadsheet file, and your Access database file (single copy only, do not submit these via JISC). These are submitted to prove that you have carried out the Access and Excel work yourself. They will only be reviewed if it is necessary to rule out plagiarism.
Please ensure that your disk/stick is clearly labelled with your Student ID number and that it and your printed report are placed in an envelope or folder so that they stay together and can be returned to you.