Max function

  • Thread starter Thread starter Pmxgs
  • Start date Start date
P

Pmxgs

Hi!

I have a table with several fields, some of which are numerical.
I've made a query where i have some calculations with those numbers, but
now i need a field in the query that gives the maximum between the fields
that i calculated. I can't put the max function to work; i'm i doing
something wrong, or the max function doesn't allow to work with several
fields?

example

field1 | field2 | field3=(field1*12-month(field1)) | field4 (should be
the max between the previous 3 fields)


thanks
 
Hi!

I have a table with several fields, some of which are numerical.
I've made a query where i have some calculations with those numbers, but
now i need a field in the query that gives the maximum between the fields
that i calculated. I can't put the max function to work; i'm i doing
something wrong, or the max function doesn't allow to work with several
fields?

example

field1 | field2 | field3=(field1*12-month(field1)) | field4 (should be
the max between the previous 3 fields)


thanks
Click to expand...

Max() - and the other domain functions - work across records, not
across fields. As a rule, in a properly normalized table, each field
refers to an independent attribute of whatever Entity (real-life
thing, person, or event) is represented by the record; having field4
depend on the values of three other fields, or field3 depend on
field1, strongly suggests that these fields should be calculated on
the fly, and should NOT be stored in any table at all.

I'd suggest storing only field1 and field2. Field3 could be calculated
by putting your expression in a vacant Field cell in a Query; Field4
could be calculated with a rather nasty nested IIF() expression or
(probably better) a small custom VBA function which would take the
three arguments and return the largest.
 
Back
Top