computed field to find the max of several fields. There must be an easier way. (1 Viewer)

[hkc94501]

I want a query that pulls four fields from a table, finds the maximum of those fields and appends that value as a computed field.
It seems simple enough and maybe I'm complicating it but I want to do this without writing a VB function. In other words I want it to be done using only SQL and and the expression builder. I have done this using IIF but its a monster! I have added line breaks to make it a more readable.
The four fields are S, F, O, and P.

iif (
IIf(Nz([Table1]![S],0)>Nz([Table1]![F],0),Nz([Table1]![S],0),Nz([Table1]![F],0))
>
 IIf(Nz([Table1]![O],0)>Nz([Table1]![P],0),Nz([Table1]![O],0),Nz([Table1]![P],0))
,
 IIf(Nz([Table1]![S],0)>Nz([Table1]![F],0),Nz([Table1]![S],0),Nz([Table1]![F],0))
,
 IIf(Nz([Table1]![O],0)>Nz([Table1]![P],0),Nz([Table1]![O],0),Nz([Table1]![P],0))
)

Seems to me there must be a better way. This method is really hard to scale.

 

[theDBguy]

Hi. Without knowing anything about your database, my guess is "normalizing" your table might be the "better way" to handle your situation. Just a thought...

 

[Gasman]

Perhaps this might help?

Microsoft Access tips: MaxOfList() and MinOfList() functions

Code for selecting the minimum and maximum values of a list of values in a Microsoft Access database. Demonstrates ParamArray.

allenbrowne.com

However still think about normalization

 

[Pat Hartman]

Relational databases are not Excel. They do not support functions that work "across" columns. Functions in a relational database work "down". They work on a SINGLE field in a set of rows rather than on a set of columns on a single row.

When you choose to not normalize your database, you need to create the "missing" functions to make it act like Excel. The better method is to normalize the data so you can use relational database techniques to work with it rather than spreadsheet techniques.

 

[GPGeorge]

I want a query that pulls four fields from a table, finds the maximum of those fields and appends that value as a computed field.
It seems simple enough and maybe I'm complicating it but I want to do this without writing a VB function. In other words I want it to be done using only SQL and and the expression builder. I have done this using IIF but its a monster! I have added line breaks to make it a more readable.
The four fields are S, F, O, and P.

iif (
IIf(Nz([Table1]![S],0)>Nz([Table1]![F],0),Nz([Table1]![S],0),Nz([Table1]![F],0))
>
IIf(Nz([Table1]![O],0)>Nz([Table1]![P],0),Nz([Table1]![O],0),Nz([Table1]![P],0))
,
IIf(Nz([Table1]![S],0)>Nz([Table1]![F],0),Nz([Table1]![S],0),Nz([Table1]![F],0))
,
IIf(Nz([Table1]![O],0)>Nz([Table1]![P],0),Nz([Table1]![O],0),Nz([Table1]![P],0))
)

Seems to me there must be a better way. This method is really hard to scale.

Here is a link to a series of blog articles on correcting this "spreadsheet style" table through normalizing it.

 

[CJ_London]

agree it would appear your data is not normalised. You can create a normalised view by using a union query inside an aggregate query, but suspect you have more fields in your table you would also want to display and summarise by. But you haven't provided that information so you will need to adjust the following sql.

SELECT max(X) FROM
(SELECT S AS X FROM Table1
UNION SELECT F FROM Table1
UNION SELECT O FROM Table1
UNION SELECT P FROM Table1) AS U