Average ACROSS FIELDS in a table

[Guest]

How do I calculate an average ACROSS FIELDS in a table in Access?

I have fields labeled length1, length2, length3, length4, length5. I would
like their average calculated by row as avg_length.

The complication is that sometimes length1...length5 are not always filled
in with values. Often times, some of the fields have no missing values so it
isn't as simple as (length1+length2...+length5)/5. (NOTE: based on other
information taken, this is the way the database needs to be set up.)

THANK YOU in advance!

 

[Ken Snell [MVP]]

Not good relational database design, which is why this is a bit tricky.

Use a UNION query to normalize the data, and then calculate average from
that:

UNION query (name it qryUnion), replacing my generic names
(PrimaryKeyFieldName and TableName ) with the real names:

SELECT A1.Length1 AS LengthField, A1.PrimaryKeyFieldName
FROM TableName AS A1
UNION ALL
SELECT A2.Length2, A2.PrimaryKeyFieldName
FROM TableName AS A2
UNION ALL
SELECT A3.Length3, A3.PrimaryKeyFieldName
FROM TableName AS A3
UNION ALL
SELECT A4.Length4, A4.PrimaryKeyFieldName
FROM TableName AS A4
UNION ALL
SELECT A5.Length5, A5.PrimaryKeyFieldName
FROM TableName AS A5;

Then use a query to calculate the Average from the above query:

SELECT T.PrimaryKeyFieldName, AVG(T.LengthField)
FROM qryUnion AS T
GROUP BY T.PrimaryKeyFieldName;

 

[Larry Daugherty]

Hi,

Your table design is incorrect for Access. It might work just fine in
Excel.

In Access, where you have an attribute that repeats (length) it should
go into a separate table: tblLength. Once you design your tables
properly you can handle any number of length records and average by
summing the lengths and dividing by the count.

Your tables should be designed to hold the records about the entities
in play in your application.

You might post back here or in microsoft.public.access.tablesdesign
with a description of your application and the entities in play.

You should probably invest in a beginning Access book or two.

HTH