Max value within a record

[Bob]

Hello,
I've designed a table that stores hourly readings for a
24 hour period containing the current date and 24 fields
representing each hour. I'm trying to extract the Max
value over a 24 hour period. Can anyone please assist me
with this?
Bob

 

[Jeff Boyce]

Bob

The aggregate functions in Access (Max(), Min(), Avg(), etc.) are designed
to work "DOWN" a column of values, not across a set of fields.

You'll have to code your own procedure and feed it the values from all 24
fields.

As an alternative approach, maybe you don't need 24 columns, one for each
hour of the day. While this may be the way a spreadsheet could be used to
handle your situation, Access is a relational database.

On the other hand, maybe what you're doing doesn't require a database -- why
not use a spreadsheet?!

Good luck

Jeff Boyce
<Access MVP>

 

[Robert L. Spaulding]

Thanks,
I was aware of the information that you provided and have been using a
spreadsheet right along. What I'm trying to do is part of a much larger
project to collect historical data on a power plant. I guess I should have
ask is anyone has an idea or example of the code I should use to pull out
the Hi and Low values for any given day.
Bob

 

[Scott McDaniel]

As Jeff pointed out, your data is not normalized, but that is often the case
with historical data. You could open a recordset against the table and
iterate through each row, examining the contents of each field:

Dim lngMax as Long
Dim rst As ADODB.Recordset

rst.Open "SELECT * FROM YourTable", CurrentProject.Connection

Do Until rst.EOF
lngMax = rst("Field1")

If rst("Field2") > lngMax Then
lngMax = rst("Field2")
End if

etc etc
Loop

rst.Close
Set rst = Nothing

Obviously, if your data is not of a Long numeric type, you would have to
alter the datatype of lngMax ... also, if the data in your "Fieldx" values
is Text, you'll probably want to explicitly convert it to a long with the
CLng(rst("Field1")) function ...