Where to put the Grade Average?

[Keli Alway]

My students take 10 tests during a class (4 classes). I need to keep a
running average of their scores per class and then their final grade.
Should this go in the query, form or report?
How could I write a formula to average grades that start with the letters
Ph1A?

 

[Keli Alway]

The title of the tests is Ph1A - History, Ph1A - Math and the grades are in
as %.

 

[KARL DEWEY]

The query would be like this --
SELECT Name, Test, Format(Avg([Grade]), "Percent" AS Grade_Average
From YourTable
GROUP BY Name, Test;

 

[John W. Vinson]

The title of the tests is Ph1A - History, Ph1A - Math and the grades are in
as %.

You know the structure and relationships of your tables. I don't.

Please explain as if to someone who cannot see your computer screen and knows
nothing about your database. I'm sure that there'll be some sort of totals
query (grouping by test name perhaps?) but I can't be specific.

 

[Keli Alway]

I no longer need to group by test name. I do; however, still need a running
average in my query. I have written the test average for the end of the
course
Course Average: ([Test 1]+[Test 2]+[Test 3])/3

How do I write for an average if they have not taken all of the tests yet?

 

[John W. Vinson]

I no longer need to group by test name. I do; however, still need a running
average in my query. I have written the test average for the end of the
course
Course Average: ([Test 1]+[Test 2]+[Test 3])/3

How do I write for an average if they have not taken all of the tests yet?

If you insist on this BADLY FLAWED AND INCOORECT DESIGN (this is spreadsheet
logic not relational design), you can use

(NZ([Test 1]) + NZ([Test 2]) + NZ([Test 3]) / (3 + IsNull([Test 1]) +
IsNull([Test 2]) + IsNull([Test 3]))

NZ will return 0 and IsNull will return -1 if the value of the test result is
null - so if there is data in Test 1 and Test 2 but Test 3 is null you'll get
Test1 + Test2 + 0/(3 - 1), thus the average of test 1 and test 2.

You'll do a LOT better to normalize your tables though, with one *record* per
test result rather than one *field*.

 

[Keli Alway]

What do you mean by "normalize your tables, with one *record* per test result
rather than one *field*?

I have a record for each student. My student table includes all their tests
and OJT. How should I have set this up differently?

I no longer need to group by test name. I do; however, still need a running
average in my query. I have written the test average for the end of the
course
Course Average: ([Test 1]+[Test 2]+[Test 3])/3

How do I write for an average if they have not taken all of the tests yet?

If you insist on this BADLY FLAWED AND INCOORECT DESIGN (this is spreadsheet
logic not relational design), you can use

(NZ([Test 1]) + NZ([Test 2]) + NZ([Test 3]) / (3 + IsNull([Test 1]) +
IsNull([Test 2]) + IsNull([Test 3]))

NZ will return 0 and IsNull will return -1 if the value of the test result is
null - so if there is data in Test 1 and Test 2 but Test 3 is null you'll get
Test1 + Test2 + 0/(3 - 1), thus the average of test 1 and test 2.

You'll do a LOT better to normalize your tables though, with one *record* per
test result rather than one *field*.

 

[John W. Vinson]

What do you mean by "normalize your tables, with one *record* per test result
rather than one *field*?

I have a record for each student. My student table includes all their tests
and OJT. How should I have set this up differently?

Three tables:

Students
StudentID
LastName
FirstName
<other biographical information, NO test results>

Tests
TestNo
Description
TestDate
<any other desired information about the test itself>

Scores
StudentID <link to Students, who took the test>
TestNo <which test they took>
Score
<any other info about THIS student and THIS test>


This will let you easily add new tests without changing the structure of your
table - just add another record in the Tests table. You can also calculate
average scores, total scores, compare scores across tests, etc. using totals
queries. You can also easily use a Form based on Students with a Subform based
on Scores (containing a combo box to select the test) to enter scores.

It's a bit tedious but not difficult to migrate your data from your current
wide-flat table into this properly normalized structure.

 

[Keli Alway]

For your information, I had already done that and this is what I designed.
I'm way past the point of stopping and starting over.

What do you mean by "normalize your tables, with one *record* per test result
rather than one *field*?

I have a record for each student. My student table includes all their tests
and OJT. How should I have set this up differently?

I no longer need to group by test name. I do; however, still need a running
average in my query. I have written the test average for the end of the

[quoted text clipped - 15 lines]

You'll do a LOT better to normalize your tables though, with one *record* per
test result rather than one *field*.

Stop. Go get a copy of Database Design for Mere Mortals. Read it. Get out
a pencil and paper. Design your database on paper. Proof your design. Once
that's okay, build your database.

 

[John W. Vinson]

That makes sense, but when I make the form, I'd like each test name to post
across the top and each student down the side rather than selecting the test.
It makes for an easy view. Would I still be able to do that with the break
out you mentioned below?

Only with a good deal more work.

You can use the non-normalized spreadsheet design with one field per test.

The cost of this is that every time you change the name of a test, or add a
test, or delete a test, you must restructure your table, redesign all your
forms that use the table, redesign all reports, rewrite all your queries. If
that cost is worth the benefit, fine.

Or you can write VBA code to migrate the data from the normalized tables I'm
suggesting into a wide- flat table for data entry, and back into the
normalized tables. Doable but pretty advanced.

Or you can use VBA code in conjunction with a third-party FlexGrid control. I
haven't worked with these but I'm told they give you a lot of control.