[AccessD] ...gurus only

...thanks gustav but:
...I'm busted again ...there was an error in the data3 max ...sigh
...given:

fields        w        x        y        z        max        series
data1        1        1                            1             2
data2        1                  5                  5             3
data3        1        4                  3        4             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 number of fields between the first one 
containing a
number and the last containing a number, not the total of the field 
contents.
3) where w/x/y/z are long integers for some stupid reason since it will 
allways be a single digit

...I ask for guru help and everyone starts exposing why I need it :(
...there is a desperate shortage of caffiene in my bloodstream ...that's my 
story and I'm sticking to it :)

William

----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, November 02, 2007 3:40 PM
Subject: Re: [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
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com