Averaging multiple fields in one record.

[shane]

I have multiple fileds I per record that I want averaged and dispalyed on a
form via text box.

I'm having a hard time figuring out, any help is appreciated.

 

[Douglas J. Steele]

No offense, but the fact that you have this requirement is usually
indicative of the fact that the table hasn't been properly normalized: that
you have a repeating group in the table which should be resolved by having a
second related table, with each of the separate fields in the existing table
actually being separate rows in the second table.

One way to resolve the problem while you're looking at correcting your
design would be to create a normalizing query that breaks the data down the
way it should be:

SELECT Field1, Field2, Field3, "Entry1" As EntryNumber, Value1 As EntryValue
FROM MyTable
UNION
SELECT Field1, Field2, Field3, "Entry2", Value2
FROM MyTable
UNION
....
UNION
SELECT Field1, Field2, Field3, "Entryn", Valuen
FROM MyTable

You can now do a query on that query:

SELECT Field1, Field2, Field3, Avg(EntryValue) As AverageValue
FROM MyQuery
GROUP BY Field1, Field2, Field3

Another approach would be to use something like:

Nz(Value1, 0) + Nz(Value2, 0) + ... + Nz(Valuen, 0)/n

as a computed field in your query.

 

[Allen Browne]

Assuming fields named A, B, C, etc, you will need an expression like this:
=(Nz([A],0) + Nz(,0) + Nz([C],0) + ...) / Abs(([A] Is Not Null) + ( Is
Not Null) + ([C] Is Not Null) + ...)

That expression will still error in a record where all the fields are null.

The real problem here is that you have built a spreadsheet in Access, not a
relational design. Whatever is in those fields, they are similar values if
you are averaging them, and so they should be multiple *records* in a
related table, instead of multiple fields in the one table. That's the most
important thing to learn to be able to use a relational database like
Access.

 

[shane]

For whatever reason I could not reply to either of you directly, so thank you
both of your replies and no offense taken. If I'm not doing something
correctly I like to be the first to know.

What I'm trying to accomplish is a centralized reporting database for our
production area. We have 12 lines, each broken down into 3 cells at 4 lines
each. The tables are designed for each cell and there are fields that have
the same sort of data for each line. Each field is specific to one line. I
take it this is something I should avoid? And that each line should have its
own table?

I have to have the forms the leaders use be the least time consuming as
possible they are used to not having to enter line numbers as they used an
Excel template. But one reason we are going to Access is to avoid having to
open a plethora of email attachments in order to read all the reports.

Reliability is one item that is reported. Reliability for each line is
manually entered into the database. To save time for those entering the
reports I wanted the average reliability to autocalculate.

 

[Douglas J. Steele]

When you post a question in a newsgroup, the expectation is that all further
correspondence about that question will be made through the newsgroup, not
through private e-mail. Both Allen and I have messages as part of our
signature lines indicating this. This should have been posted as a followup
to your original post, so that everything is available all in one thread.

That having been said, we're definitely not recommending that each line
should have its own table. You should have a single table to represent all
12 lines, not 3 separate tables, one per cell. Included as part of the table
should be fields to identify to which cell and which line that specific row
applies. In other words, rather than rows in three separate tables, each
representing 4 lines in a particular cell, you'd have 12 rows in one table.

Jeff Conrad has a lot of links to good resources on this topic at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Another thing is that the design of appropriate tables and the design of
appropriate forms are not necessarily related. You should always start by
designing the tables appropriately. Once you know that all of the necessary
data will be available in the tables (and only then), you can design your
forms in whatever approach makes the most sense to your users.

Incidentally, you spelled both my name and Allen's incorrectly. <g>

 

[shane]

My apologies for misspelling your Name Mr. Steele and Mr. Browne.

I did not mean emailing either of you but responding directly to each of
your posts.

Thanks for the link and the advice, it was helpful.