Maximum Across Fields

[bgetson]

I've got a query where I want to find the maximum value from among 4
dimensions for each record in my tables.

If in tblSettings, I have fields: Length1, Length2, Length3, Length4,
how can I easily calculate the maximum length for each record (instead
of maximum for each field)? I've heard that I can do something like
this with iif(), but I can't get my head around it. If it helps at
all, there is another field, TestID, which can serve as an identifier
for each record.

Thank you for any help.

 

[Jeff Boyce]

If you have 'repeating fields' (e.g., "Length1, Length2, ...), you most
likely have ... a spreadsheet! Consider exporting the data to Excel and
doing your Max() calculation there.

In MS Access, the Max (and Min, and Count, and ...) functions are
set-related, not field-related. In other words, if your data is in a
well-normalized structure, an Access Totals query can calculate a max or min
or ... quite easily.

If you data is organized more like a spreadsheet, you will have to come up
with work-arounds (IIF() expressions, user-written procedures, etc.) to get
the same thing done.

And if ANYTHING about the repeating fields changes (the number of fields,
....), you will have to modify your table structure, your queries, your
functions, your reports, your forms, ... a HUGE maintenance burden.

Consider checking into normalizing your data before proceeding...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I've got a query where I want to find the maximum value from among 4
dimensions for each record in my tables.

If in tblSettings, I have fields: Length1, Length2, Length3, Length4,
how can I easily calculate the maximum length for each record (instead
of maximum for each field)? I've heard that I can do something like
this with iif(), but I can't get my head around it. If it helps at
all, there is another field, TestID, which can serve as an identifier
for each record.

The problem is that you should probably have those Length
fields in a separate table. Then you could use the Max
function.

If you are stuck with the current table structure, then you
can create a function to find the max value. There's a good
one at http://allenbrowne.com/func-09.html

 

[bgetson]

Jeff: While you're right that the example I gave you would make a lot
more sense as a worksheet within Excel, I needed the result from that
calculation to be populated within Access, so that it could be used it
other calculations throughout my database. Thank you for your help on
database maintenance. I'm still new to databases, VBA, and SQL - but
I'm learning.

Also, the function linked on Allen Browne's website worked perfectly!
I almost feel embarrassed by my attempts at nested IIF()'s after
looking at that solution. Now I've just got to figure out how it all
works so I can use it for future projects.....

Thanks again for all your help.