Maximum value of 2 columns in the third

[kenny]

hi everybody,

i have a question which may be rather trivial to some of you:

in my scenario i have three columns with col 1 and 2 holding integers
and i want to know the respective maximum for each row in col 3.

i´ve been using something with max(...) but it works only for 1
parameter. i want to use only sql, no further vb... would be great if
someone could help.... thx!
Yours, Kenny Ajram

[pietlinden]

kenny wrote:

hi everybody,

i have a question which may be rather trivial to some of you:

in my scenario i have three columns with col 1 and 2 holding integers
and i want to know the respective maximum for each row in col 3.

i´ve been using something with max(...) but it works only for 1
parameter. i want to use only sql, no further vb... would be great if
someone could help.... thx!
Yours, Kenny Ajram

Kenny,
storing this is bad design. Use a query instead. And MAX works on a
single field, and not really across fields. You can use IIF to do
this. Something like...

SELECT tblA.FieldA, tblA.FieldB,
IIF(Nz([fieldA],0)>=Nz([FieldB],0),Nz([FieldA]),Nz([FieldB])) As
MaxTest
FROM tblA
....

[Marshall Barton]

kenny wrote:

>i have a question which may be rather trivial to some of you:

in my scenario i have three columns with col 1 and 2 holding integers
and i want to know the respective maximum for each row in col 3.

i´ve been using something with max(...) but it works only for 1
parameter. i want to use only sql, no further vb... would be great if
someone could help.... thx!

The fields in a table are supposed to be dependent on
**only** the primary key. If you can compare two fields in
the same table, there is likely to be a normalization flaw
in your table design.

The Max (and all other aggregate) functions operate on a
single field in all of the records so you are on the wrong
track. To find the larger of two values use:
IIf(a b, a, b)
If you need to do this for more than two values, I strongly
suggest that you create a public function (in a standard VBA
module), instead of coming up with ever increasingly complex
expressions.

--
Marsh

[kenny]

thank you so far, but the problem still remains. the code is pretty
much what i want, but i get a syntax error, saying i should put the
expression in brackets... i´m sure i have used the right syntax... i
ceated a third field and used the "build" menu entry to create the
query....

SELECT tblA.FieldA, tblA.FieldB,
IIF(Nz([fieldA],0)>=Nz([FieldB],0),Nz([FieldA]),Nz([FieldB])) FROM tblA

i also used

SELECT tblA.FieldA, tblA.FieldB,
IIF([fieldA]>=[FieldB]),[FieldA],[FieldB]) FROM tblA
the same error.... :-((

[kenny]

it´s done, thx for your help :-)))