Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2015
    Posts
    1

    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.

    Vern

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    Code:
    SELECT format (SamplePoint, "YYYYMMDDHH"),  max(FlowRate) as MaxFlowRate
    FROM MyTable
    group by format (SamplePoint, "YYYYMMDDHH")
    order by format (SamplePoint, "YYYYMMDDHH");
    then use that in another qeury (as a subselect or just join to the original table) join on the formatted sample times are the same
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •