Average, minimum and maximum values in MS Access 2003 if some columns are null
Posted on 2013-09-10
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...