[AccessD] ...gurus only

Hi William

Here is one method using Choose():

  SELECT 
    ID, 
    w, 
    x, 
    y, 
    z, 
      IIf(Val("" & [w])>Val("" & [x]),
        Val("" & [w]),
        Val("" & [x])) AS Max12, 
      IIf(Val("" & [y])>Val("" & [z]),
        Val("" & [y]),
        Val("" & [z])) AS Max34, 
      IIf(Val("" & [w])>0,
        1,
        IIf(Val("" & [x])>0,
          2,
          IIf(Val("" & [y])>0,
            3,
            IIf(Val("" & [z])>0,
              4,
              0)))) AS 
    First, 
      IIf(Val("" & [z])>0,
        4,
        IIf(Val("" & [y])>0,
          3,
          IIf(Val("" & [x])>0,
            2,
            IIf(Val("" & [w])>0,
              1,
              0)))) AS 
    Last, 
      IIf([Max12]>[Max34],
        [Max12],
        [Max34]) AS 
    Max, 
      IIf([First]=[Last],
        Val(Choose([First],[w],[x],[y],[z])),
        Val(Choose([First],[w],[x],[y],[z]))+Val(Choose([Last],[w],[x],[y],[z]))) AS 
    Series
  FROM 
    tblGuru;


This will return, which differs from your example but is according to your specification:

Max	Series
1	2
5	6
4	4
3	5
1	2
1	1

/gustav

>>> wdhindman at dejpolsystems.com 02-11-2007 19:45:35 >>>
...ok, you too, eh :)

...given:

fields        w        x        y        z        max        series
data1        1        1                            1             2
data2        1                  5                  5             3
data3        1        4                  3        3             4
data4                  2        3                  3             2
data5        1        1        1        1        1             4
data6                  1                            1             1

1) where max is the largest number in w/x/y/z and
2) where series is the total fields between the first one containing a 
number and the last containing a number
3) where empty fields contain "" rather than nulls

...two days and I've yet to come up with an answer to fill the max and 
series fields from the data in w/x/y/z.

...help!

William