Database structure

[Kaylen]

I want to make a table that record students tests scores. The table
fields/columns are : Student Name, Test1, Test2, Test3, etc across. Then I
want to run a report that calculate a specific student average scores for
Tests 1, Test 2 , and Test 3. Can someone help me with structuring the table
and coming up with a expression to calculate the average? Any help is
appreciated.

 

[akphidelt]

You can actually create 1 table...

ID Number, Name, Test1, Test2, Test3

Then to get averages of the students you can create a report and in a
textbox on the report footer you can type in the formula in the Control
Source of the textbox.

=Avg([Test1])

 

[Kaylen]

What if I want to calculate the average of three tests of that student: Test
1, test2, and test3. What would the formula be?

You can actually create 1 table...

ID Number, Name, Test1, Test2, Test3

Then to get averages of the students you can create a report and in a
textbox on the report footer you can type in the formula in the Control
Source of the textbox.

=Avg([Test1])

I want to make a table that record students tests scores. The table
fields/columns are : Student Name, Test1, Test2, Test3, etc across. Then I
want to run a report that calculate a specific student average scores for
Tests 1, Test 2 , and Test 3. Can someone help me with structuring the table
and coming up with a expression to calculate the average? Any help is
appreciated.

 

[Bob Quintal]

You can actually create 1 table...

bad move. Next week, you need a fourth test, so you have to modify
the table, the queries, the forms and the reports that are based on
the table

The proper structure would be
StudentID, TestID, TestScore
BQ01 1 82
KL04 1 77
GH12 1 55
BQ01 2 91
KL04 2 79
GH12 3 69
KL04 3 91


For each test you simply add a row to the table.

To get averages from a normalized data set like this, all you need
to do is create a Totals query with the following SQL

SELECT StudentID, avg(testscore) FROM testScores GROUP BY studentID

To get the average of the scores for tests 7 and 8

Just add to the query WHERE TestID = 7 OR testID = 8