Is conditional averaging possible?

[Invoice]

I have the following situation:

1. A Homeroom table which includes student id, academic year, and homeroom.
2. A Student Grades table which includes student id (linked to Homeroom table), term, academic year, progress grade (15 pts), and progress grade (A-F).

The contents of these tables are imported from excel, and that works fine except for one thing: in order to keep the field [progress grade (15 pts)] numeric, I had to put a 0.0 as a grade for any student who got an "I" (Incomplete) or "N/A" as his or her progress grade.

The problem arises when I want to produce an average progress grade for the student for that given term/year. How do I not include the 0.0's of students who got I's or N/A's?

Any help would be much appreciated.

 

[RV]

Define your column as text.
In your query, exclude N/A and I.
Convert your grade column to a numeric string in your query.

RV

 

[Invoice]

I agree with you wholeheartedly but the problem is that my data is being imported from an excel calculated spreadsheet. It doesn't seem possible to create a null value entry in Excel because all the data is calculated. At most a zero value "" can be produced in a cell, but then Access will not accept that in a numerical field. What do you think?

Alternatively, is there any way to do a conditional count calculation in my access report? E.g., for a particular student, count the number of letter grades he got which weren't "I" or "N/A"?

You need to use null instead of 0. Otherwise, how could you tell a real zero from a N/A? Aggregates in Access will ignore null values. So the average of -
3,0,3 would be 2 (as you have discovered) whereas the average of 3,null,3 would be 3.

If you leave the zeros, which I strongly recommend against, you will need to add selection criteria to exclude zero values from the domain.

Select ...
From ...
Where YourGrade <> 0;

 

[RV]

Alternatively, is there any way to do a conditional count calculation in my access report? E.g., for a particular student, count the number of letter grades he got which weren't "I" or "N/A"?

Referring to my previous reply.

RV

 

[RV]

Yes RV, you can count these items but having a text field that is really a numeric field is poor design

Quite right.
However sometimes you simply want a workaround
In this case you could argue whether the field is numeric or text, so no big deal.

RV

 

[Invoice]

There's no problem putting in the word "Null" instead of "" in the calculated numerical column back in Excel (if I've understood you correctly), but when I tried to import into Access, I got two tables of import errors.

Is there something I'm doing wrong?

In case I was unclear earlier, the problem is with my numerical column in excel, the one with student scores in it. Somehow I have to record the null value in the excel file and then send it to excel. "" gets a problem, and "null" seems to as well. That's why I was on the 0 tip.