Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: Can this be done without recordset?

    Can this be done without recordset?

    I have a table with four columns:

    Price1
    Price2
    Price3
    Lowest

    I'd like to select the lowest price row by row and place it in the lowest column. It would look something like this when it's done:

    Price1 | Price2 | Price3 | Lowest
    -------------------------------------
    1 | 4 | 7 | 1
    -------------------------------------
    23 | 22 | 9 | 9
    -------------------------------------

    I'd like to do something like:

    UPDATE TABLE1 SET LOWEST = MINVALUE (Price1, Price2, Price3)

    Can this be done with one question? I've allready solved the problem with function that does this with a recordset, but I'm curious.

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Smile

    UPDATE TABLE1 SET TABLE1.Lowest =
    IIf([price 1]<[price 2] And [price 1]<[price 3],[price 1],
    IIf([price 2]<[price 1] And [price 2]<[price 3],[PRICE 2],[PRICE 3]))

  3. #3
    Join Date
    Jul 2003
    Posts
    50
    Just what I was looking for! But...

    What if some values are NULL? The function should ignore NULL-values, ie should return the lowest number...

    I have tried some more nested Iifs but don't seem to figure this problem out.

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    If the entry are only price-value, why do you have NULL value's ?
    They shoud be 0,00
    and if you want to exclude those too, you coud see that as a condition also
    SO
    iif Price1 <> NULL (not isnull / null = false) AND PRICE1 < PRICE 2 ect OR
    iif Price1 > 0,00 AND PRICE1 < PRICE 2 ect

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm not in favour of replacing the nulls with 0 in this "lowest" problem: it makes the problem more complicated because 0 is always the cheapest price.

    better is to replace nulls with an impossibly high number (i.e. max of whatever is the datatype)

    so you have two stages.

    first query:
    [A'] = iif(isnull([A]), 99999, [A])
    etc [B'], [C']
    replacing A B C with the real names, and 99999 with the max possible value for the datatype

    second query:
    iif([A']<[B'], iif([A']<[C'], [A'], [C']), iif([C']<[B'], [C'], [B']))

    izy
    Last edited by izyrider; 07-22-03 at 17:41.

  6. #6
    Join Date
    Jul 2003
    Posts
    50
    Yeah, but now it's two questions... I think the VB-function is faster then two updates.

    The function first selects a number greater than 0 if there is one.
    Then it assumes this is the lowest price and checks against the other prices and selects the lowest and not NULL.

    The thing is there are never any 0s in the prices, only NULL if there isn't any price. Too bad, otherwise the first question would have worked.

Posting Permissions

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