Average, minimum and maximum values in MS Access 2003 if some columns are null
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)
... 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!
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)
... 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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
It may need a function to do the job. The table needs a primary or unique key.
What maximum number records are you anticipating>?