Creating an "Average" expression

[GIDATS4978]

I am trying to return all values or both Null and 99 as 0's and then average the 5 fields in the query by the number of fields that have data greater than 0.

My original expression: (Nz([1A],0)+Nz([1B],0)+Nz([1C],0)+Nz([1D],0)+Nz([1E],0))/((IIf(Nz([1A],0)>0,1,0))+(IIf(Nz([1B],0)>0,1,0))+(IIf(Nz([1C],0)>0,1,0))+(IIf(Nz([1D],0)>0,1,0))+(IIf(Nz([1E],0)>0,1,0)))

Which works great...however when I enter 99 in any of the fields it counts 99 in the calculations.

I've tried using the "Or" statement in the expression but couldn't figure it out.

The fields will have data from 0 to 5 or 99 as a "Not-Observed". I want to return all values as their value only if it is Not Null or if it is not 99 (i.e. 1,2,3,4,5 or ">0<99").

I know I could put this range in the Criteria of each field, but the entire query needs to be shown (including all Null's and 99's). That's why I created an expression to get the averages.

Thanks in advance for any assistance.

 

[plog]

Sounds like you need to restructure your table. When you have fields that need to be added/counted/averaged together, they shouldn't be in the same record. Each value should be in its own field. Perhaps this means you make a new table with a many to 1 relationship with the one you currently have.

When a value shouldn't be present (e.g. not observed), you simply don't have a record for it. When you structure your table properly you are able to use SQL aggregate functions (MAX, MIN, SUM, COUNT, AVG). That means to get the averages you would simply do this:

SELECT AVG([YourFieldNameHere]) FROM YourTableNameHere

 

[GIDATS4978]

I'm not sure how I would go about restructuring the table.

The table that this query pulls from right now has all of the student's grades for each of the "subjects" which will be the 1-5 score and the 99 as "Not-Observed".

I already have a table with all of the student's demographic information seperately.

 

[The_Doc_Man]

Your problem is a design flaw (and possibly a table layout flaw).

You need to define what happens in the following cases:

If a grade is 99, does that mean the student didn't take the course, missed a test, didn't participate, whatever?... and does that mean that whereas everyone else might have, say, 4 grades, the one who has a 99 has only 3 grades? In which case if that person has scores of 4, 4, 4, 4, 99 - will the average be (4+4+4+4)/4 or (4+4+4+4)/5 ?

Understand that you and only you can truly understand your problem. We can only tell you the syntax needed to get it done. But as described, your table grossly violates the principles of normalization that would otherwise allow this to be a much simpler problem.

 

[GIDATS4978]

I pulled all of the data into one table from an existing Excel spreadsheet. My supervisor now wants an Access database which I've been working on.

I have designed it so far where every student has a main form page with subforms to other information (demographic page, admission information, insurance information, grades, etc.) all from different tables with the StudentID as the linked field to all of the tables. Is this not a good way to go about it?

In my scenario the student has scores of 4, 4, 4, 4, 99 - and I need it to be the average of (4+4+4+4)/4 (basically getting rid of the 99 score completely until that student is given an actual grade (1 thru 5).

I hope this clears it up as I wasn't trying to confuse anyone.

How might I go about redesigning the tables to make it easier?

 

[plog]

Can you post a screenshot of your relationship view? That's the screen where you layout how your tables relate.

 

[GIDATS4978]

Here's a jpg of the relationships I have with tables and queries. Let me know if you need any other information.

 

[plog]

I was hoping to see the fields of your tables. Can you post the actual database because your screenshot shows only about 5 fields per tables, and not the ones you initially discussed.

 

[GIDATS4978]

Sorry, I was working on another project yesterday. Here is my database. I zipped it so it would be able to be attached. Thanks for the help.

 

[plog]

Yeah, you've got a lot of work to do on your tables. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), that's the process of correctly structuring your tables. Here's the big things I see in a couple of places:

1. Numerated field names. When you start numbering fields (1A, 2A, 3A, etc.), it's time for a new table. Tables should grow vertically (with more rows) and not horizontally (with more fields). So, instead of 5 columns for your A values you should have a table with 5 records instead.

2. Valued field names. Same as issue as numerated field names with the same resolution--a new table. You don't store values in a name. Expected Practia Scores does this. Instead of a field for each Type (e.g. Relational, Science, etc.) you need a record for each type along with a field to define what type it is.

Again, start with your tables and get those properly structured before you move on to reports/forms.

 

[GIDATS4978]

The numerated fields names are the abbreviations for the "subjects" they are graded on. I felt that instead of typing out the entire name of each "subject" I would use the number. For example 1A is "Integrity", 1D is "Concern for the Welfare of Others" and then 1A thru 1E is under the overall heading of "Professionalism". I should type out all the subjects? Seems like a lot of tables. Are more tables better?

Should I have a table for each Overall Heading (6 of them) broken down by each Subject (54 of them broken up amongst the Overall Headings) they are getting graded on?

It looks like I've been trying to create my tables as Excel spreadsheets which may be my problem. Too much data on one table?

Not sure what you mean by valued fields?

This database started out very simple and easy and has grown to be frustrating with the amount of data my supervisor wants.

 

[plog]

More records, not more fields nor more tables. Let's use Practica Competencies as an example. Currently you have this (skipping a few fields):

ID, ..., Professional, Relational, Science, Application, Education, Systems, ...
43, ..., 3, 3, 2, 3, N/O, 2, ...
42, ..., 5, 5, 3-4, 5, 3, N/O, ...

Professional, Relational, etc. are valued field names. You are storing relevant data in the names of fields. Instead, all those values go into a new table with just 4 fields:

PracticaCompetenciesScores
pcs_ID, ID_pc, pcs_Type, pcs_Score
1, 43, Professional, 3
2, 43, Relational, 3
3, 43, Science, 2
4, 43, Application, N/O
...
7, 42, Professional, 5
8, 42, Relational, 5
...
12, 42, Systems, N/O

pcs_ID is an autonumber primary key
ID_pc is a foreign key to the ID field in Practica Competencies
pcs_Type, text, will hold the value that you currently have in field names
pcs_Score, text, will hold the value stored in all those fields

Also, if those values need to be treated as numbers (added, averaged, ordered, etc.) then you should make pcs_Score a number field and not store non-numeric values. If 'N/O' represents Null or not applicable, then you wouldn't even have a record for it in PracticaCompetenciesScores.

I would also advise a few nomenclature rules---Avoid spaces in field/table names (Practica Competencies=>PracticaCompetencies) and don't call every ID field 'ID', prefix it with something so you don't get them all confused.

 

[GIDATS4978]

I appreciate all of your help.

I'm restructuring my tables. Here is what I have so far.

CompetencyTable
Comp_ID
Comp_Name (Only 2 -- "Foundational" & "Functional")

PrimarySubjectTable
Prim_ID
Comp_ID (from CompetencyTable...The first 3 Prim_Name fall under "Foundational" and the last 3 Prim_Name fall under "Functional")
Prim_Name (6 Primary Subjects -- "Professionalism", "Relational", "Science", "Application", "Education", "Systems")

SecondarySubjectTable
Sec_ID
Prim_ID (from PrimarySubjectTable)
Sec_Name (These are the 54 Secondary Subjects that each fall under one of the Primary Subjects)

ScoresTable
Scores_ID
Sec_ID (from SecondarySubjectTable)
Student_ID (Unique identifier from StudentsTable)
Pcs_Score (Numeric Field for Practica Competencies Score...data entered by their "Secondary" Adviser)
Rev_Score (Numeric Field for Annual Review Score...Same data as Practica Competencies but entered by their "Primary" Adviser)

I understand the Numeric vs. Text Field. However, my Supervisor only wants the Null value if the Adviser's didn't enter any value in the grading form at all (So he can yell at the Adviser's for not entering in all of the grades or filling out the hand-written form correctly). So Null values would be separate from "N/O"'s.

Again, thanks for your help with this. I'm starting to understand it better now and it seems like it'll be easier to navigate.