Hi all

I am trying to create a join query (recordset) in excel dao, which is essentially made up of two queries.

This recordset works fine, But I only want to return the Max [frDate] for each of the EmpNo's.

sql = "SELECT EmpNo, RotaCode, frDate, toDate, Type, Comments " & _
          "FROM tblRotaCode " & _
          "WHERE (frDate <=#" & Format(DateFrom, "mm/dd/yyyy") & "# " & _
          "AND toDate >=#" & Format(DateTo, "mm/dd/yyyy") & "#) " & _
          "OR (frDate <=#" & Format(DateFrom, "mm/dd/yyyy") & "# " & _
          "AND toDate Is Null) " & _
          "ORDER BY EmpNo, frDate DESC;"
If I write standard sql and only select the EmpNo and the frDate i return the correct records, just not all the fields.

This returns just the Max frDate for each EmpNo.

sql = "SELECT EmpNo, Max(frDate) AS MaxOfFromDate " & _
          "FROM tblRotaCode " & _
          "WHERE (((frDate) <=#" & Format(DateFrom, "mm/dd/yyyy") & "#) " & _
          "AND ((toDate) >=#" & Format(DateTo, "mm/dd/yyyy") & "#)) " & _
          "OR (((frDate) <=#" & Format(DateFrom, "mm/dd/yyyy") & "#) " & _
          "AND ((toDate) Is Null)) " & _
          "GROUP BY EmpNo, RotaCode;"

This is my attempt at combining the two (but obviously not working), just trying to explain fully.

Not Working:
sql = "SELECT EmpNo, RotaCode, frDate, toDate, Type, Comments " & _
          "FROM tblRotaCode INNER JOIN ( " & _
              "SELECT EmpNo, Max(frDate) AS MaxOfFromDate " & _
              "FROM tblRotaCode " & _
              "WHERE (((frDate) <=#" & Format(DateFrom, "mm/dd/yyyy") & "#) " & _
              "AND ((toDate) >=#" & Format(DateTo, "mm/dd/yyyy") & "#)) " & _
              "OR (((frDate) <=#" & Format(DateFrom, "mm/dd/yyyy") & "#) " & _
              "AND ((toDate) Is Null)) " & _
              "GROUP BY EmpNo, RotaCode" & _
          " ) ON (frDate = MaxOfFromDate) AND (EmpNo = EmpNo) " & _
          "ORDER BY EmpNo, frDate;"
Hope this all makes sense... any help appreciated.

Thanks