Return Max Value in Row

[Gsurfdude]

Hello,

I have a table that contains 24 hours of data. I need to find the max # in
what ever hour in that 24 hour period. For example, I am selecting the Max of
hours 1-24 for the month of April which returns the MAX of hours 1-24. I need
to get the highest #. My query retuns one row with max values for all 24
hours but I Need to find the "peak" hour. Any help and does this make sense ?

Thanks in advance

 

[Allen Browne]

So you have 24 fields, each with a value?

That is not the best way to build the table. It would be *much* more
flexible and easy to query if you used a table with fields:
TheDate (Date/Time)
TheHour (Number)
TheValue
Then there would be 24 records for each date, and it is dead-easy to find
the largest one.

If you want to proceed with your current data structure anyway, see:
MinOfList() and MaxOfList()
at:
http://allenbrowne.com/func-09.html

 

[John Vinson]

Hello,

I have a table that contains 24 hours of data.

Then you have a misdesigned table. If you have a one (day) to many
(hours) relationship, you should have TWO tables in a one-to-many
relationship. You may want to consider just storing one record per
hour rather than one record (with 24 repeating fields) per day; a very
simple totals query would then let you find the peak.

I need to find the max # in
what ever hour in that 24 hour period. For example, I am selecting the Max of
hours 1-24 for the month of April which returns the MAX of hours 1-24. I need
to get the highest #. My query retuns one row with max values for all 24
hours but I Need to find the "peak" hour. Any help and does this make sense ?

You'll need to write a VBA function to step through all the fields;
this is too many for a nested IIF to work.

John W. Vinson[MVP]