Calculate average (1 Viewer)

[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!

 

[Uncle Gizmo]

Don't get it.... Need more info....

Table 1
StudentName, StudentID, Q1, Q2, Q3, Q4, Q5, Q6
you say are 0 or 1 answers,

but the other table shows questions?

 

[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.

 

[Uncle Gizmo]

At the bottom of this thread "Excel in Access (Part 1)" there is a tool I created for transposing data from the flat file type of your questions table into something more suitable for use within MS Access.

The tool is designed to be used with any suitable table, however if it transposes your data correctly, and you can make use of the new data format, then I believe you could modify this general tool into a simple push button operation that you could use time and time again.

 

[echorley]

Thanks, I will take a look.

 

[Uncle Gizmo]

If you read the threads "excel in access (part one)" I think you should be able to get a general idea of what to do.

However for clarity, I would use the Tool on your student table, you should then end up with a new table with the following format:

StudentID --- question name --- question result
132---------------Q1-------------------0
132---------------Q2-------------------0
132---------------Q3-------------------0
132---------------Q4-------------------1
132---------------Q5-------------------1
132---------------Q6-------------------1
139---------------Q1-------------------0
139---------------Q2-------------------1
139---------------Q3-------------------1
139---------------Q4-------------------1
139---------------Q5-------------------0
139---------------Q6-------------------0
102---------------Q1-------------------0
102---------------Q2-------------------1
102---------------Q3-------------------0
102---------------Q4-------------------1
102---------------Q5-------------------0
132---------------Q6-------------------1
456---------------Q1-------------------0
456---------------Q2-------------------1
456---------------Q3-------------------0
456---------------Q4-------------------0
456---------------Q5-------------------1
456---------------Q6-------------------0

The above information is derived from your student table, which you should still have, you can now delete the question data from your original student table. (you don't have to but it would be tidier)

Now change the question identities of your question table, (alternatively add a new field), in which you would place a question identifier to match the transposed field Headings: Q1, Q2, Q3 ....

Now you should have enough information to link all the tables back together and query them to your heart's content.

 

[echorley]

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

 

[Uncle Gizmo]

>>>I was just hoping there was a "slicker" way in Access only<<<

It will be in access, the Tool is an MS Access form.
It should be pretty slick as you put it. With the code in the form I'm reasonably sure you should be able to automate the process into a single button push.

By the Way, I couldn't download your sample database, it showed up as an *.mdb file in the download window but when it appeared on my machine it had a different extension? I don't know whether this was a problem with the download or whether it's a problem with my machine. However I have downloaded numerous files without this problem before.

 

[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.