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

Posted on 2013-09-10
Medium Priority
Last Modified: 2013-09-19
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:


... 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...

Question by:curtwade
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))

Open in new window

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)) 

Open in new window

However, this trick will not work for AVE, as I believe with average you would want to omit the NULL values altogether.
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>?
LVL 74

Assisted Solution

by:Jeffrey Coachman
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:
...Then MIN() will be 5

If, on the other hand, you have:
...the min is the Zero

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


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!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question