# Thread: Can this be done without recordset?

1. Registered User
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. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450
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. Registered User
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. Registered User
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. Cavalier King Charles
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. Registered User
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
•