Not Null calculation not working as expected (1 Viewer)

TMullins · May 14, 2018

I'm trying to calculate the number of columns that are not null so I can accurately calculate the average of our student's grades per record. I've included an attachment from the query. Sometimes I get numbers that don't make any sense to me at all.

The expressing I used in the Not_Null column is:
Not_Null: Sum(IIf(
[A1] Is Null,0,1)+IIf(
[A2] Is Null,0,1)+IIf(
[A3] Is Null,0,1)+IIf(
[A4] Is Null,0,1)+IIf(
[A5] Is Null,0,1)+IIf(
[A6] Is Null,0,1)+IIf(
[A7] Is Null,0,1)+IIf(
[A8] Is Null,0,1)+IIf(
[A9] Is Null,0,1)+IIf(
[A10]Is Null,0,1))

The Average column is incorrect because it takes the sum of A1-A10 and divides by 10 regardless of if any 1 or more columns are null or not.

Any assistance is greatly appreciated

plog · May 14, 2018

You need to set up your tables correctly. You don't have a database, you have a spreadsheet. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization).

Once you structure your data correctly this becomes a trivial query:

Code:

SELECT Student, AVG(Grade) AS GradeAverage
FROM YOUR TABLE
GROUP BY Student

Data should be stored vertically (with more records) and not horizontally (with more columns). In your data 1 record contains 10 grades, the proper way to store that data is with 10 records with 1 column for the grade. You get it into that structure and you can use the query I posted--no Iifs or IsNulls needed.

Again, read up on normalization.

The_Doc_Man · May 14, 2018

Are you working in Excel and then importing data to Access? Or is this all in Excel? Your description doesn't seem consistent with what Access does, when talking about the function you named. In that context, you are talking about an Excel function which would not give you anything related to averages.

Your rows and columns are not set up in a way for Access to be able to do much. Let's start by asking whether this is Access or Excel. The next question is to ask from where your data originates?

Let's be clear, what you want to do can be done in Access - but not in the way you are apparently doing it. So we need to clear the air regarding environment and data sets, hence my two questions.

EDIT: I see that plog beat me to it. He sees the same thing I see, we just have different ways of stating it.

TMullins · May 14, 2018

I do have a database, and my fields are normalized. Perhaps I'm just doing a bad job at explaining myself. I apologize for that.

Our road scores are divided between 10 different elements and the final score is the average of the above mentioned elements. I simply renamed them A1-A10 to make it easier to remember and type.

Each line represents 1 record for each student for what is usually a 4 hour segment at our school.

Let's say that the student didn't go on the interstate that day, they would get a null score. In this instance, the average would be graded on the average of the other 9 elements instead of 10.

TMullins · May 14, 2018

The_Doc_Man said:
Are you working in Excel and then importing data to Access? Or is this all in Excel? Your description doesn't seem consistent with what Access does, when talking about the function you named. In that context, you are talking about an Excel function which would not give you anything related to averages.

Your rows and columns are not set up in a way for Access to be able to do much. Let's start by asking whether this is Access or Excel. The next question is to ask from where your data originates?

Let's be clear, what you want to do can be done in Access - but not in the way you are apparently doing it. So we need to clear the air regarding environment and data sets, hence my two questions.

EDIT: I see that plog beat me to it. He sees the same thing I see, we just have different ways of stating it.

When I first took over this job, I created ad-hoc spreadsheets to help me with grading and keeping track of our student's road hours. I quickly realized that I needed to use a database, so I've been on working that. All of my entries that you're seeing here was entered directly in a Access 2016.

I apologize for not wording my questions the right way or for not taking good screen shots that would help to fully explain that I'm doing.

Thank you for taking the time to try to help me with this.

isladogs · May 14, 2018

TMullins said:
I do have a database, and my fields are normalized. Perhaps I'm just doing a bad job at explaining myself. I apologize for that.

Our road scores are divided between 10 different elements and the final score is the average of the above mentioned elements. I simply renamed them A1-A10 to make it easier to remember and type.

Each line represents 1 record for each student for what is usually a 4 hour segment at our school.

Let's say that the student didn't go on the interstate that day, they would get a null score. In this instance, the average would be graded on the average of the other 9 elements instead of 10.

Sorry to disagree but I don't believe your tables are normalised e.g. fields like Test_Hours, Class_Hours, Oil_Hours, Heavy_Hours should be changed to an HourValue & HourType field.
Your Road table contains a large number of R_ fields (all with cumbersome names) and is effectively a spreadsheet. Almost certainly, these fields should be changed as well & definitely should be renamed
If you gave the fields clear concise names, there would be no need to use A1/A2/A3 etc in your query

The answer plog gave you earlier still applies

TMullins · May 14, 2018

ridders said:
Sorry to disagree but I don't believe your tables are normalised e.g. fields like Test_Hours, Class_Hours, Oil_Hours, Heavy_Hours should be changed to an HourValue & HourType field.
Your Road table contains a large number of R_ fields (all with cumbersome names) and is effectively a spreadsheet. Almost certainly, these fields should be changed as well & definitely should be renamed
If you gave the fields clear concise names, there would be no need to use A1/A2/A3 etc in your query

The answer plog gave you earlier still applies

Even if I rename the fields as you described, I would still have the SAME issue that I have here.

isladogs · May 14, 2018

TMullins said:
Even if I rename the fields as you described, I would still have the SAME issue that I have here.

Yes you would if you just rename them ... but not if you normalise the tables

TMullins · May 14, 2018

ridders said:
Yes you would if you just rename them ... but not if you normalise the tables

I've renamed some of the fields so that they make more sense. I can see where I could break the Students table more.

jdraw · May 14, 2018

Normalization link to RogersAccessLibrary

TMullins · May 14, 2018

I've read these many helpful posts and links and seen several videos on normalizing.... what, specifically, am I missing ??

I've attached a picture of our 'road card'. Here, you can see that there are 10 graded elements which, when averaged together, make up the final 'Grade' for that segment. I'm simply trying to calculate this 'Grade'.

plog · May 15, 2018

A database should not have a table like this:

StudentID, GradeMath, GradeHistory, GradeEnglish, GradeBiology
1, 91, 79, 86, 70
2, 89, 88, , 68

That is not normalized because you are storing a value in a field name. The course itself should be in a field, not in a name. So, the correct way to score that data is like so:

StudentID, Subject, Grade
1, Math, 91
1, History, 79
1, English, 86
1, Biology, 70
2, Math, 89
2, History, 88
2, Biology, 68

Not 4 columns for grades, but 1 record for each grade. Then I can use the AVG() function like I illustrated above and it does the math for me (including only using 3 as the denominator for Student=2 because they only have 3 classes--no need for IIfs or IsNulls.)

Your input card has no bearing on how the tables of your database should be structured. Data normalization is dictated by the data you are storing not virtual or hard copy forms.

TMullins · May 15, 2018

Thank you very much... Now I'm going to have to wrap my mind around how to make a form to make that happen.

Student, Element, Grade
1, Starting-Stopping-Braking, 3.5
1, Sharing_the_road, 3.5
1, Steering_Turning, 3.5
1, Lane_Changing_Passing, 3.5
1, Vehicle_Spacing , 3.5
1, City_Driving, 3.5
1, Shifting_Stalling, 3.5
1, Interstate_Highway, 3.5
1, Maneuverability, 3.5
1, Safe_Driving_Practice, 3.5

I added another table, called Road_Elements and posted a picture below.

So my road grade table could look something like this:

Student, Element, Grade
1, 1, 3.5
1, 2, 3.5
1, 3, 3.5
1, 4, 3.5
1, 5, 3.5
1, 6, 3.5
1, 7, 3.5
1, 8, 3.5
1, 9, 3.5
1, 10, 3.5

I think I'm repeating what you're saying to get this normalized ?

Pat Hartman · May 15, 2018

Use a subform to enter the grades. If all grades are required and there are never any additional tests, then in the AfterInsert event of the main form, you can run an append query that appends the 10 grade records so all you have to do is to enter the score for each.

TMullins · May 15, 2018

Pat Hartman said:
Use a subform to enter the grades. If all grades are required and there are never any additional tests, then in the AfterInsert event of the main form, you can run an append query that appends the 10 grade records so all you have to do is to enter the score for each.

Occasionally, the student just observes and gets no grade, but they do get credited for the time that they spent, without a grade. That is, however, rare. Most of the time, all 10 records are logged with the rare exception that the student didn't shift (automatic truck), or didn't go on the interstate that during their driving time.

Being new to Access, I created a form in datasheet view and everything appears to be working. - I attached screen shots.

Pat Hartman · May 16, 2018

The nice thing about Avg() is if you leave the grade null, the row will be ignored. So
the average of:
3, null, 3 is 3
but the average of
3, 0, 3 is 2

isladogs · May 16, 2018

Don't forget that once you have fixed your current issue, you need to deal with normalising your Students table as well - see my original reply

The_Doc_Man · May 16, 2018

The issue with normalization is the rather bizarre - but correct - phrase "like likes like." You normalize to keep very closely related things together and keep less closely related things apart. They can still be related - but will be separate.

Look at it this way. You have students taking your driving skills tests. OK, that's enough grounds for a table of students. You will probably have some sort of Student ID number and more data about the student, like name, address, home phone, cell phone, gender, ... All of those things are things about the student and nothing else. Simple enough, right?

You have driving skills, each of which differs from the other skills in some way but they are all closely related in that they involve wheeled vehicles and actions you can take with them. That's close enough for a skills table. Do you see that concept?

Now you have grades. Do they belong to students? Yes and no. Because you ALSO have to ask if they belong to the tests. And there, the answer is ALSO yes and no. (If you don't like "belong" then use some variant of "associated".)

This is where the concept of a Junction table comes in, where you link a grade to both a student and a skill. If you want the students' average grades, you build a query that includes TWO significant parts:

(A) SELECT STUDENTID, AVG(GRADE) FROM STUDENTSKILLS ... and
(B) GROUP BY STUDENTID

The exact structure of that query will depend on the actual table names you use and whether you try to JOIN student names to student IDs.

Note that since this is an "SQL aggregate" query (as evidenced by the AVG function), there are some restrictions on what else you can SELECT.

If you have already gotten that far, then I apologize for being a bit too pedantic. But that is the thought process that goes into normalization.

TMullins · May 17, 2018

ridders said:
Don't forget that once you have fixed your current issue, you need to deal with normalising your Students table as well - see my original reply

I would like to thank everyone here for opening my eyes. I thought I was doing a good job of normalizing, but obviously I wasn't. Thank you for pointing out the errors of my ways without being condescending or hurtful. It's greatly appreciated. I'm working on the Students table now.

TMullins · May 17, 2018

Pat Hartman said:
The nice thing about Avg() is if you leave the grade null, the row will be ignored. So
the average of:
3, null, 3 is 3
but the average of
3, 0, 3 is 2

I've been very careful with data entry to either make the entry null, (or in one of my above examples, I left the entry completely out), so as to not have the undesirable result you mentioned.

Not Null calculation not working as expected (1 Viewer)

Registered User.

Attachments

Banishment Pending

Immoderate Moderator

Registered User.

Attachments

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

Super Moderator

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Super Moderator

Registered User.

Attachments

Super Moderator

MVP / VIP

Immoderate Moderator

Registered User.

Registered User.

Similar threads

Users who are viewing this thread