Average Without Including Null/0 Across Fields

[ARW17]

I want to average in the following way, but can't figure it out.

T1 T2 T3 T4 T5 Average
0 12 10 8

I want the answer in the average column to be 10 (12+10+8)/3, not (0+12+10+0+8)/5.

I want something similar to AVERAGEIFS in Excel...

I've read a bunch of posts, but it seems like they're averaging within a single field.

Help please!

 

[Joe4]

It seems to me that you have a design issue. The design of your table is not normalized.
In a normalized table, you would not have 5 repeating similar fields (T1, T2, T3, T4, and T5).
You have 5 records, which the field structure of something like "T number" and "T value".

T_number      T_value
1                 0
2                12
3                10
4                 8
5

Then you can get the average pretty easily with a simply Aggregate Query:

SELECT Avg(TableName.T_value) AS T_Avg
FROM TableName
WHERE TableName.T_value<>0;

Good table design is important in Access. If you design your tables the right way, you can usually do most things you want. If you do not design them correctly, even seemingly simple tasks like this one become difficult.
There are lots of good articles on Database Normalization out there on the web. Here is but one (Google search will produce many others): https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics

 

[ARW17]

Sorry, but I'm confused.

I have different queries that have fields in them that I'm pulling into a different query.

I want to average what the T value was over time (though sometimes I don't have a value).

Query 1 has a field called T0, query 2 has a field called T1, etc...

Is there no way to average by record across these times in a query? I could sum Query1!T0 + Query2!T1 + .... I can't do an average this way?

Do I need to do a cross tab to average vertically instead of horizontally?

 

[Joe4]

You are thinking in an Excel worksheet kind of way, and not in a relational database kind of way.
Aggregate functions (like average) are typically down on a single field across multiple records, not on multiple fields in a single record.

Depending on how your data is structured and related, you may be able to use combine them in a single query using a Union query (see: http://www.fmsinc.com/microsoftaccess/query/union-all/index.htm)
Can you give us the structure of the individual queries (Query1, Query2, etc), and tell us how they are related to each other?

 

[ARW17]

I ended up just adding them up and counting them if they weren't blank then dividing the sum by the count.

 

[Joe4]

I ended up just adding them up and counting them if they weren't blank then dividing the sum by the count.

That is a manual workaround that will work, and may not seem like a big deal now, but if that structure is left like that, it could present problems down the road. For example, what happens if you have to add more T levels (say, 10)? You would need to update all your queries, and possibly your tables too. With a well-designed database, you should seldom have to add new fields and update your queries like that. If you find yourself doing that a lot, it is a sign that you may have a design flaw.

 

[ARW17]

Thanks!

 

[Joe4]

You are welcome!

Unfortunately, I had to learn the hard way myself. My first database project was a big one, and I kept running into issues like these. For example, we had 23 different fields for 23 different funds. Then my manager would come to me and say that we are getting rid of a certain fund, so look for all the clients that use that fund. Should be simple, right? Well, with that kind of structure, it involves either having to create one query that has 23 different criteria (one to check each fund), or 23 different queries!

And it first started out that companies could have 18 funds. Then some had 20, and then later 23. Every time it increased, I was updating my tables to add more funds, which them meant I needed to also update all the queries, forms, and reports! What a nightmare!

I was spinning my wheels for a while on issues like these when someone pointed me to Database Normalization and Relational Database concepts. Six months into my project, I had to scrap the whole thing, and start over, but it worked SO much better afterwards. As matter as fact, almost 20 years later and they are still using that database, even though I left 2 years ago (because it seldom needs its structure modified)!