Multiple Field Average 4

[puforee]

Good morning. I have a table of values. Five fields of values in the table belong to a common theme. Not every record has a value in each field. I can create a query that will give me the average for each field. However...I need a query/calculation that will give me the average of all 5 fields. There by giving me the overall average for the common set of values.

Can anyone help please?

 

[dhookom]

IMO, this is not normalized. It seems a bit like a spreadsheet. In the future it would be good to post the table structure or at least the significant table and field names.

You can create a normalizing union query to get the average:
== quniNormed ===

Select PKField, [Fld1] as Fld
FROM tblOfValues
WHERE Fld1 Is Not Null
UNION ALL
Select PKField, [Fld2]
FROM tblOfValues
WHERE Fld2 Is Not Null
UNION ALL
Select PKField, [Fld3]
FROM tblOfValues
WHERE Fld3 Is Not Null
UNION ALL
Select PKField, [Fld4]
FROM tblOfValues
WHERE Fld4 Is Not Null
UNION ALL
Select PKField, [Fld5]
FROM tblOfValues
WHERE Fld5 Is Not Null;

Then create a group by

SELECT PKField, Avg(Fld) as FldAvg
FROM quniNormed
Group BY PKField

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

try
SELECT PKField, Avg(nz(fld1,0)+nz(fld2,0)+nz(fld3,0)+nz(fld4,0)+nz(fld5,0)) as FldAvg
FROM tblOfValues
Group BY PKField

 

[dhookom]

I think PWise and I understood differently. I guess it's important to provide some sample records and desired output to cut down on the confusion.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK. Rec=Records, F=Field.

F1 F2 F3 F4 F5
Rec 1 1 5 4 4
Rec 2 3 5 4 2
Rec 3 4 5
Rec 4 5 5 5 5 3

So Avg 3.3 4.3 4.6 4.5 2.5 Averages of averages 3.86 (Wrong)

Overall average should be: 4 Correct

I need the final calculation (4)

Thanks,

 

[dhookom]

You still didn't provide the names of significant fields and tables.

You could take my initial union query and create a query from it as:

SELECT Avg(Fld) as FldAvg
FROM quniNormed

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

So, you wanted this ?
SELECT (Nz(Sum(F1),0)+Nz(Sum(F2),0)+Nz(Sum(F3),0)+Nz(Sum(F4),0)+Nz(Sum(F5),0))/(Count(F1)+Count(F2)+Count(F3)+Count(F4)+Count(F5))
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[puforee]

Lets call the table Critiques
Fields. There are about 50 fields but only 5 that I am concerned about.
Fields:
CE My schedule was accurate
CE The course schedule met my needs
CE The course duration was appropriate
CE The training facilities were suitable
CE Overall I am satisfied with my training experience

Does this help?

 

[dhookom]

puforee,
It helps. Are you going to tell us if any of our help was of value? Did you try any of it?

Do you understand your table structure might not be normalized?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Yes to all...I am still working on it. I will let you know.

But I do thank everyone for the help.

 

[MichaelRed]

Why do you regard the valus of the averages (3.86) as wrong?

While there are variations in the thought of calculating averages (principally "Simple" and "Weighted" average(s). You should realize that the (aparent) column calculations are ignoring 'null' values. This is the normal / accepted / documented behaviour for MS products (MS Access and Excel for SURE).

Given these "Column" averages (your list "So Avg ... " the 3.86 is the correct value.

It APPEARS (to me) that you are expecting the sum of all the values (60?) to be divided by the sum of the column sums(15?). But what rationale are you using for this?



MichaelRed

 

[dhookom]

Puforee is summing the values (60) and dividing by the count (15) which is 4.00.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

This is precisely what I suggested (12 Sep 11 11:44)

 

[puforee]

Everyone. I am so happy about all the responses.

First..the 3.86 is wrong because it is an average of averages. It should be the total values divided by the total count. This is mathematically more correct.

I tried PWise technique and it worked...But...I only showed you a part of the data that I have to be analyzed. So, because of the number of fields (22) I decided to go with a standard Totals query returning the Sum and Count for each of the 22 fields. I then group this by Month Year and put it in a table. I use excel to further analysis the data from the table. My users use the spreadsheet to graphically show the data. It is tedious but is the simplest for me. Now I have to do it 4 more times with other data. Wish me luck.

I will be saving this chain of conversations for future reference.
I thank you all very much.

 

[dhookom]

Sum and Count for each of the 22 fields ... I then ... and put it in ... use excel to ... do it 4 more times with other data

This is the penalty for not having your table structure normalized.

Duane
Hook'D on Access
MS Access MVP