How to calculate the average of columns

[Ilina Ivanova]

Please, help me!
I have a DB (Access 2007) with 4 columns. The first is the ID of the patient and the others are indexes PB1 , PB2, PB3. I have difficulties to calculate the average of columns PB1, PB2, PB3, because when 2 fields are filled the average needs to be divided by 2. Zero is a value too.
Thanks!

[NeoPa]

So you want the average of all fields which have data in them (including zeroes).

In that case, try :

SELECT ID
     , [PB1]
     , [PB2]
     , [PB3]
     , IIf(IsNull([PB1] & [PB2] & [PB3]),
           Null
         , (Nz([PB1],0) + Nz([PB2],0) + Nz([PB3],0)) /
           (IIf(IsNull([PB1]),0,1) +
            IIf(IsNull([PB2]),0,1) +
            IIf(IsNull([PB3]),0,1)) AS [Avg]
FROM   [YourTable]

Welcome to Bytes!

[Ilina Ivanova]

Thank you, NeoPa,
I`ve tried it, but it is impossible to run the query because of message „ ... the query does not include the specified expression „ID” as a part of an aggregate function”.
I am a beginner in Access and probably I make a mistake.

[NeoPa]

This is a message you would get if there were any aggregate calls in your SQL. There is none in what I posted, but there is a name ([Avg]) which could be misinterpreted if not copied in correctly. Why don't you post the actual SQL you're using and I'll look at it for you. Alternatively, try grabbing this again (from post #2) and copy/paste it in to your project exactly as it is. If that works then no need for further worry.

[Ilina Ivanova]

Sorry NeoPa. I`ve tried many times including copy/paste after your recommendation but without any result. The expression disappears after I run the query or a message of incorrect subquery`s syntax appears.
Is it possible to e-mail you the file for help me.

[NeoPa]

No to email Ilina, but it's perfectly possible to attach a copy to a post in here which should suffice.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[Ilina Ivanova]

I have one table [Status] with four columns (ID, PB1, PB2, PB3) (the data in the other fields is not relevant to my question and I removed it). I want to create a query [Index] showing the ID in one column and the average of PB1,PB2,PB3 in second column. I have difficulties to build the expression for average because there are records with missing values and I need the sum to be divided by 2 or 1. Zero is a value too.

[NeoPa]

Ilina, you have not left in any of your attempts to paste in the code I suggested, so it will be hard for me to tell you where you went wrong.

Let me look at it and see what I can do for you.

[NeoPa]

It seems I left a closing parenthesis (')') from the end of the suggested SQL. Not hard to do when working blind. Once I tried it the error message (different from any you posted) made it clear what the problem was and where. Here's the updated SQL :

SELECT ID
     , [PB1]
     , [PB2]
     , [PB3]
     , IIf(IsNull([PB1] & [PB2] & [PB3]),
           Null
         , (Nz([PB1],0) + Nz([PB2],0) + Nz([PB3],0)) /
           (IIf(IsNull([PB1]),0,1) +
            IIf(IsNull([PB2]),0,1) +
            IIf(IsNull([PB3]),0,1))) AS [Avg]
FROM   [Status]

Let me know if you still have problems getting it to work. It worked perfectly for me by the way (Your example data was perfect to test with).

[Ilina Ivanova]

Thank you, NeoPa! It`s really work! Thank you very, very much! It was so usefully joining the Bytes.com Expert Community!