max value and corresponding time

[dewpaul]

Hello,

I have a table with three fields: Date, Time, and, Concentration. I am
able to find the maximum concentration value ("Concentration" field) by
using the aggregate "max" function in the "Concentration" field grouped by
"Date". With this I get the date and the maxiumum concentration for that
date. In addition, I want to get the time from "Time" which corresponds to
the max concentration. In the end I would have three columns: Date,
MaxConcTime, and MaxConc.
Can you suggest a method for this?
Thanks!

 

[Jeff Boyce]

Access uses "Date" and "Time" as reserved words. If your table/query uses
these same words as fieldnames, both you and Access will be confused.

Have you looked into Totals queries?

Good luck

Jeff Boyce
<Access MVP>

 

[dewpaul]

Hi Jeff,
I am new to Access so I only used "Date" and "Time" as example field
names...bad choice. My field names are different than any reserved words.
So I am looking to get the time of each maximum concentration for each day.
Any other suggestions? As I explained I am currently getting the max
concentration grouped by day. I want to include the time of the max
concentration occurrence in the query.

Thanks for your input

 

[Jeff Boyce]

One approach would be to use your first query, the one that determines the
max(Concentration) for each Date, then build a second query that joins that
first query back to the underlying table, matching on both date and
concentration, and return the Time.

Good luck!

Jeff Boyce
<MS Access MVP>