Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Unanswered: An Equivalent of Excel's Match/Index Functions

    Hi,

    My apologies if this has been covered before, but I've been unable to find a solution. My problem is that I would like to recreate the match/index functions of Excel in my MS Access queries.


    My query, as it stands, looks something like this

    ID Sea Water Rock Scrub MaxVal Use
    1 10 10 10 70 70 ?
    2 20 30 50 0 50 ?


    So I would like to be able to have a function which will look for the MaxVal value and return the relevant column heading to the Use column?


    Any assistance would be greatly appreciated
    Thanks
    mj

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You have to hard code an expression for this, using (for example) iif().

    The underlying reason is that Excel and Access (a relational database management system) operate on totally different principles. I can't say for sure without knowing more but it looks like you might have a first normal form problem:
    http://www.tonymarston.net/php-mysql...se-design.html

    If your table was instead structured:

    Code:
    ID    Type    Val
    ---------------
    1     Sea      10
    1     Water   10
    1     Rock     10
    ......
    2     Sea       10
    ........
    then your query would be:
    Code:
    SELECT id, MAX(Val)
    FROM myTable
    GROUP BY id
    In short, aggregate functions in SQL operate across rows, not columns.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2008
    Posts
    4
    thanks for your reply, but unfortunately the each ID in the ID column represents a 1km x 1km cell for joining to spatial data, so they must all be unique.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could do it with a nicely nested IIf function... sorta.

    But poots is right... the resulting query of the SQL example given could then be related to your spatial data.

    Here's a question for you.. in your example, which column name would you want returned for Use? Two of them match... in both lines.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mj14
    thanks for your reply, but unfortunately the each ID in the ID column represents a 1km x 1km cell for joining to spatial data, so they must all be unique.
    Have a read of the article. Regarding that point, keep an eye out for "Composite Keys".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2008
    Posts
    4
    Sorry, bad formatting.

    At the moment I've written a function to return the Maximum values in each row to MaxVal, now I would like the Use column to hold the name of the column which contains that maximum value

    ID.....Sea.....Water.....Rock.....Scrub.....Max... ...ValUse
    1.......10........10.........10........70......... 70.........?
    2.......20........30.........50.........0......... .50.........?


    I shall have a closer read of the http://www.tonymarston.net article

    thanks again

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd agree this has the firm smack of poor design
    thanks for your reply, but unfortunately the each ID in the ID column represents a 1km x 1km cell for joining to spatial data, so they must all be unique.
    you could have a sub table with the ID and the measurement type with a value.... getting the maximum value and the measurement type is trivial
    eg
    measurementtypes
    id
    description

    sample
    id
    date
    description
    ...etc

    sampledata
    sampleid 'fk to id in sample
    measurementype 'fk to measurementtypes
    value

    however if you must stick with your design
    depending on where yu are using this it may be easier to do this in code. if you try to do this in a query then you will probbaly need to duplicate the iif statements. just thinking about it the IIF would be a nightmare.

    If its in a report then put some code in the reports on format event, if its a form in the forms on current event.

    essentially I'd suggest you create a single function, and call that function from the relevant event hook in forms or repots
    your function could return the value or the description OR it could return tht index of the highest value

    function getmaxindex(SeaVal as integer,WaterVal as Integer,RockVal as integer, scrubval as integer) as integer
    if seaval>waterval and seaval>rockval and seaval>rockval and seaval>scrubval then
    getmaxindex=1
    elseif waterval>seaval and waterval>rockval and waterval>scrubval then
    getmaxindex=2
    elseif rockval>seaval and rockval>waterval and rockval>scrubval then
    getmaxindex=3
    elseif scrubval>seaval and scrubval>waterval and scrubval>rockval then
    getmaxindex=4
    else 'we have no clear winner
    getmaxindex=0
    endif

    then you decode that index to get the value and the description

    as an alternative
    function getmaxvalue(SeaVal as integer,WaterVal as Integer,RockVal as integer, scrubval as integer, ReturnType as integer) as variant
    'returntype determines waht element ofdata is returned
    '0= is the value (the default)
    '1= the desrption
    if seaval>waterval and seaval>rockval and seaval>rockval and seaval>scrubval then
    if returntype = 0 then
    getmaxvalue=seaval
    else
    getmaxvalue="Sea"
    endif
    elseif waterval>seaval and waterval>rockval and waterval>scrubval then
    '..insert appropriate getmaxvalue statements as above
    elseif rockval>seaval and rockval>waterval and rockval>scrubval then
    '..insert appropriate getmaxindex statements as above
    elseif scrubval>seaval and scrubval>waterval and scrubval>rockval then
    '..insert appropriate getmaxindex statements as above
    else 'we have no clear winner
    '..insert appropriate getmaxindex statements as above
    endif

    you would need to call this twice
    eg
    select id,sea,water,rock,scrub, getmaxvalue(sea,water,rock,scrub,0) as maxvalue, getmaxvalue(sea,water,rock,scrub,1) as Usevalue from mytable

    this is air code, so it may(!) not work, and there may(!) well be errors
    hth
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2008
    Posts
    4
    Thanks everyone for your advice. There is a traditional problem in my organisation with poor database design. The reason that I needed (or rather wanted) the data in the original format was that it would I was hoping to make the process quicker to meet a deadline, however, it will be so much better long term to re-structure the database and correct the problems.

    Thanks again
    mj

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    mj14 - I wrote a long (and incredibly witty I might add) post last night that got eaten by the DBForums bug. The gist of it was that a very common error is to try to cross apply what one knows about spreadsheets to databases. Sometimes one has to beat people over the head with this for weeks before it sinks in - good job on spotting the correct path .

    A crucial rule with databases - time put in at the beginning on the model will pay dividends later when you are trying to build the damn thing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And maintain it.... And use it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •