Find the Maximum Value by Date

[Guest]

I have several years' worth of electrical load profile data on half-hour
intervals. I want to develop a query that can give me a table of the maximum
values for each day, with the associated date and time for that value.
Basically, there are 48 data points per day, and I want the highest single
value on each day, sorted by date. Any ideas how to do this?

 

[Guest]

Interesting problem. But before I or anyone else jumps on it, there are some
questions.
1. Are the dates and times in one or two fields?
2. The names and datatypes of all the fields in question.
3. Name of the table(s). If more than one table, which field is in which and
how are the tables linked.

 

[Tom Ellison]

Dear Mark:

This may be a starting point:

SELECT DateValue(ReadingDateTime), MAX(ReadingValue)
FROM YourTable
GROUP BY DateValue(ReadingDateTime)
ORDER BY DateValue(ReadingDateTime)

Now, the ReadingDateTime means that column of your data containing the date
and time of readings, and ReadingValue means the column containing those
half hour readings. Change these column names to what is in your table.
Also, replace YourTable above to the actual name of your table.

Does this help? If there are other details about your situation, please
explain and I'll try again.

Tom Ellison

 

[OfficeDev18 via AccessMonster.com]

Hi, Mark,

You'll need two levels for this one.

SELECT LoadDate,Max(LoadVal) As MaxOfLoadVal FROM YourTableName GROUP BY
LoadDate;

Of course, you need to substitute the real table name and field names. Save
that query, maybe call it qryMaxLoadSub. Now a new query:

SELECT LoadDate, LoadTime, MaxOfLoadVal FROM YourTableName INNER JOIN
qryMaxLoadSub ON YourTableName.LoadDate = qryMaxLoadSub.LoadDate WHERE
YourTableName.LoadVal = qryMaxLoadSub.MaxOfLoadVal;

When you run this second query, you should get something like:

1/1/2006 2:30:00 PM (Some load value)
1/2/2003 (some time) (some value)
etc.

Of course, you can add sorting and other display options to your upper-level
query. Don't forget to put in the real names of your table and fields.

Hope this helps,

Sam