By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Maximum value of 2 columns in the third

P: n/a
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.

ive 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

Jan 15 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a

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.

ive 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
....

Jan 16 '07 #2

P: n/a
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.

ive 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
Jan 16 '07 #3

P: n/a
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... im 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.... :-((

Jan 22 '07 #4

P: n/a
its done, thx for your help :-)))

Jan 23 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.