Calculate average

[echorley]

I have two tables, one containing the result of each student's answer to a multiple choice question.

StudentName, StudentID, Q1, Q2, Q3, Q4, Q5, Q6

In the fields Q1, Q2, etc, are 1's and 0's, 1 if the student got the question correct and 0 if they did not.

In another table is the standard that each question number is attached to.

Question_Num, StandardName,StandardDescriptor

For example:

1 N.12.00.A Can add two digit numbers
2 N.13.00.S Can subtract two digit numbers
3 N.12.00.A Can add two digit numbers
4 N.13.00.S Can subtract two digit numbers
5 N.13.00.S Can subtract two digit numbers
6 N.25.01.P Can solve number problems

What I would like is a query that would combine the two tables to find the average of each standard, for each student. For example:

StudentName, StudentID, Avg_N.12.00.A, Avg_N.13.00.S, Avg_N.25.01.P

Right now I export and import the information back and forth between Access and Excel to compute each student's standard average. But I am hoping to automate the process in Access.

The tables above are how the information is retured to us from our state Department of Education.

Thanks!

 

[echorley]

Thanks for the reply. The first table is a listing of each student and how they performed on each question of the test. 1 stands for correct and 0 stands for incorrect. For example:

ADAMS BOB J 000125 0 1 1 0

would imply that the student got the 1st and 4th questions wrong and the 2nd and 3rd questions correct. This is how we get it back from the Dept. of Ed. after statewide standardized testing.

Then the next table has 3 fields, the question number, the standard it is attached to and a description of that standard. For example:

1 N.01.SDS Add two digit number
2 N.22.RMD Can find the area of a square

The 1 and the 2 in the example above correlate to the 0 and 1 respectively.

I have attached a small sample database with the two tables.

Thanks again.

 

[echorley]

Thanks, I will take a look.

 

[echorley]

That will work, I was just hoping there was a "slicker" way in Access only. Thanks for the Excel tools.

 

[Poppa Smurf]

I had no problem downloading the sample database
Attached is an updated your database. I have added a field for the student id as there was no field in your sample. There is a module called mod_transpose_results that transposes your results data into a table called tbl_results.

Note, this will work with your data but if there isa change to data structure in the results data or the standrds table then the coding will need to be changed.

From the tbl_results you should be able to determine the average.

 

[echorley]

Wow, thanks. The original data has over 3000 students and each test has between 50 and 70 questions, so I am going to try to change the code to create some loops.