By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 435,426 Members | 3,338 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 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. 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 Jan 15 '07 #1
Share this Question
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. 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 .... 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 integersand 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 1parameter. i want to use only sql, no further vb... would be great ifsomeone 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... 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.... :-(( Jan 22 '07 #4

 P: n/a it´s done, thx for your help :-))) Jan 23 '07 #5

### This discussion thread is closed

Replies have been disabled for this discussion.