max value and corresponding time

  • Thread starter Thread starter dewpaul
  • Start date Start date
D

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!
 
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>
 
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
 
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>
 
Back
Top