02-10-15, 12:20 #1Registered User
- Join Date
- Feb 2015
Unanswered: Returning 2 Values Based On 1 Maximum Value, Access 2010
I have one table which stores the following information: [DateTime], [Flow] (number), and [Chemical Concentration] (number).
I need to find the maximum hourly flow for the day which is a no brainer. I group on "YYMMDDHH" and set the query result to MAX. My problem is this, at the maximum flow for the hour, I also have to record the Chemical Concentration. If I try and do this in the same query, the only choices I have are Max, Min, First, Last, etc. for the Chemical Concentration. I've tried concatenation followed by sorting but I'm getting nowhere.
Any thoughts would be welcome.
02-11-15, 06:36 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
how do you resolve ties, is it possible that you may have more than one row with the same flowrate in the same time analysis period?
what you coudl do is run one query which returns the maximum flow rate per hour
..thats going to be somehtign like
SELECT format (SamplePoint, "YYYYMMDDHH"), max(FlowRate) as MaxFlowRate FROM MyTable group by format (SamplePoint, "YYYYMMDDHH") order by format (SamplePoint, "YYYYMMDDHH");I'd rather be riding on the Tiger 800 or the Norton