Solved

# Average, minimum and maximum values in MS Access 2003 if some columns are null

Posted on 2013-09-10
Medium Priority
2,100 Views
Excel 2007 can do it, but I cannot find a way in Access 2003 to do it: find the average, minimum and maximum values of multiple columns in a table if some of the columns' numeric values are blank/null for a given record.  Here is some sample data for five columns in three records:

C1      C2      C3      C4      C5
275        275                    275
272        274                    272
270        271                                          272

... where the values in Columns C3 and C4 are null/blank for these three records.

I also need to have other columns in Access to indicate the minimum and maximum values in C1 to C5 for each record, even with the nulls.

Currently, the values in this Access 2003 table are being queried into an Excel 2007 sheet, and I can do formulas like this in a separate column in Excel, for example, to highlight the MS Access C1 to C5 mismatches via Excel conditional formatting:

=IF(MIN(A2:E2)<>MAX(A2:E2),"X","")

... where Acccess' C1 data is in Excel's Column A and Access' C5 data is in Excel's Column E.

But it REALLY slows down filtering and sorting in the Excel file (with 30,000+ records), so I'd like to have Access do these calculations and put them into columns in the table being queried by Excel.

I'll be on vacation until next Mon 16-Sep, so if you do not hear back from me immediately, that's why...

Thanks!
0

LVL 25

Accepted Solution

chaau earned 1000 total points
ID: 39482084
There is a function in Access called Nz(). It allows to replace the value for null to another value.

You can use together with MIN and MAX to get better values.

So, fox MAX you would use
``````MAX(Nz(C1,0), Nz(C2,0), Nz(C3,0), Nz(C4,0), Nz(C5,0))
``````

For Min you need to replace the NULL values to some big number
``````MIN(Nz(C1,1234567890), Nz(C2,1234567890), Nz(C3,1234567890), Nz(C4,1234567890), Nz(C5,1234567890))
``````

However, this trick will not work for AVE, as I believe with average you would want to omit the NULL values altogether.
0

LVL 31

Expert Comment

ID: 39482675
Upload a sample database with the table. Add test records including all possible variations of data, and list the required output.
It may need a function to do the job. The table needs a primary or unique key.
What maximum number records are you anticipating>?
0

LVL 74

Assisted Solution

Jeffrey Coachman earned 1000 total points
ID: 39483436
Yes, ...in some cases NZ() may return the incorrect results.

a Null is not considered in any mathematical calculations, where as a Zero will be.

So if you have:
45
13
77
5
null
83
...Then MIN() will be 5

If, on the other hand, you have:
45
13
77
5
NZ(NULL)
83
...the min is the Zero

So as hnasr stated, sometimes it is best if you post a sample database and include the expected results.

JeffCoachman
0

LVL 1

Author Closing Comment

ID: 39507280
The MS Access NZ function is the key to what I am looking for - thanks!  But trying a query formula like this in Access:

MAX(Nz(C1,0), Nz(C2,0), Nz(C3,0), Nz(C4,0), Nz(C5,0))

... returns a 'The expression you entered has a function containing the wrong number of arguments' error.

I dug around and found the two MinOfList and MaxOfList functions at http://allenbrowne.com/func-09.html which does exactly what I need.

Thanks, folks!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.