title: Search for Max Date and return max date item along with other fields for that record
body: <p>I have three tables:</p>
  <ul>
  <li><p>WorkOrder that contains all of the Work Orders that have been done on MaintItems, (A MaintItem will occur multiple times in the WorkOrder Table)</p>
  </li>
  <li><p>MaintItems that contains a list of Unique MaintItems,</p>
  </li>
  <li><p>LastOccu to store the Last Occurrence details from the WorkOrder table of the MaintItem.</p>
  </li>
  </ul>
  <p>To identify the last date that a MaintItem was performed and to populate the LastOccu table with the MaxDate and MaintItem works correctly. The Problem that I have is when I want to update the lastOccu table with the additional WorkOrder Number field, the query then returns all WorkOrders for that MaintItem.</p>
  <blockquote>
  <p>WorkOrder table example:</p>
  <pre><code>WorkOrder MaintItem   RefDate     Reading
  1         101         2018/01/30  200
  2         103         2018/02/03  1200
  3         101         2018/02/04  230
  </code></pre>
  </blockquote>
  <p>LastOccu table result required:</p>
  <pre><code>MaintItem MaxDate    WONumber Reading
  101       2018/01/30 3        230
  103       2018/02/03 2        1200
  </code></pre>
  <p>The Query that I need help with as follows:</p>
  <pre><code>INSERT INTO LastOccu ( MaintItem, MaxDate, WONumber, ReadingNo )
  SELECT MD.MaintItem, MD.MaxRefDate, MD.WONumber, MD.ReadingNo
  FROM 
  (SELECT MI.MaintItem, MAX(WO.RefDate) AS MaxRefDate, WO.WONumber,
  WO.ReadingNo FROM MaintItem AS MI INNER JOIN WorkOrder AS WO ON 
  MI.MaintItem = WO.MaintItem WHERE WO.RefDate is not null GROUP BY
  MI.MaintItem, WO.WONumber, WO.ReadingNo)  AS MD;
  </code></pre>
  <p><em><strong>Also note that I'm using MS Access</strong></em></p>
  <p>Help will be greatly appreciated!</p>