Query for Averageing

[RobinF]

Can someone help me set up a query where the totals of
the fields are added up and then divided by the number of
fields to obtain an average.

However, if there is no number in a field then the
average is to not include that field in the averge.

e.g. Field1 Field2 Field3 Field4 Avg
10 8 12 25 13.75 = Total / 4

10 25 8.75 = Total / 4

What I want Is not Total / 4 but Total / no of field with
numbers So I Need Total / 2 becouse only 2 fields have
numbers.

Thank You

 

[MGFoster]

Can someone help me set up a query where the totals of
the fields are added up and then divided by the number of
fields to obtain an average.

However, if there is no number in a field then the
average is to not include that field in the averge.

e.g. Field1 Field2 Field3 Field4 Avg
10 8 12 25 13.75 = Total / 4

10 25 8.75 = Total / 4

What I want Is not Total / 4 but Total / no of field with
numbers So I Need Total / 2 becouse only 2 fields have
numbers.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If this is a table it is incorrectly designed. If the table was
designed correctly you could use the SQL function Avg() to get the
correct average. E.g.:

Table Design:
ID The record id - no nulls
NumberField Your number field - can be null

Get the average:

SELECT ID, Avg(NumberField)
FROM TableName
GROUP BY ID

Using your data as the example you'd get the resultset of:

ID NumberFieldAvg Raw Data
------- -------------- --------
1 13.75 (10+8+12+25)/4
2 17.5 (10+25)/2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHICLIechKqOuFEgEQL5jACgschsevDbfN1YFODc/uOZOIrVnj8AoJ3L
ocd1sKukYEM/BE9bYarrYy5t
=lTky
-----END PGP SIGNATURE-----

 

[TracyL]

-----Original Message-----


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If this is a table it is incorrectly designed. If the table was
designed correctly you could use the SQL function Avg() to get the
correct average. E.g.:

Table Design:
ID The record id - no nulls
NumberField Your number field - can be null

Get the average:

SELECT ID, Avg(NumberField)
FROM TableName
GROUP BY ID

Using your data as the example you'd get the resultset of:

ID NumberFieldAvg Raw Data
------- -------------- --------
1 13.75 (10+8+12+25)/4
2 17.5 (10+25)/2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHICLIechKqOuFEgEQL5jACgschsevDbfN1YFODc/uOZOIrVnj 8AoJ3L
ocd1sKukYEM/BE9bYarrYy5t
=lTky
-----END PGP SIGNATURE-----

.
I'm having the same issue. I agree that the table is

incorrectly designed but is there a way to get around
this? We are not always in control of table design and in
this case it's a 3rd party survey given to me to analyze.
Any ideas - or do I need to convert the data to a properly
designed table?

 

[John Spencer (MVP)]

One method is to use a UNION query to normalize the data and then use that as
the source for the calculation vice the table,

SELECT PrimaryKey, "Field1" as ItemType, Field1
FROM theTable
UNION ALL
SELECT PrimaryKey, "Field2", Field2
FROM theTable
UNION ALL
SELECT PrimaryKey, "Field3", Field3
FROM theTable
UNION ALL
SELECT PrimaryKey, "Field4", Field4
FROM theTable

This will work as long as there are not TOO many fields

Then to get the desired results

SELECT PrimaryKey,
Avg(Field1) as AverageResponse
Count(Field1) as CountResponses
FROM SavedUnionQuery

You could even use the union query as the source of a make table (or append)
query to restructure the data.