Preview
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.
Background
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.
The tables in the file are discussed next. Figure 1 shows the first few records of the Year1Students table.
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.
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.
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.
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.
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.
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.
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.
| Teacher | Year1 Rating by Criterion 1 | Year 1 Rating by Criterion 2 |
| Smith | ||
| Jones | ||
| Casey |
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.
MARKING CRITERIA
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.
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]