title: How to select top 1 in Access query - and actually get it to work
body: <p>First let me point out that this is a repeat of multiple stack-overflow questions that all have answers - yet none of them solve my problem</p>
  
  <p>For instance - these two:
  <a href="https://stackoverflow.com/questions/6128351/access-join-on-first-record">Access join on first record</a>
  and
  <a href="https://stackoverflow.com/questions/9339079/how-to-select-top-10-in-access-query">How to select top 10 in Access query?</a></p>
  
  <hr>
  
  <p>My problem should be simple - but - the obvious answers are not working.</p>
  
  <p>I have two tables.  Client and Transactions.</p>
  
  <p>I want to return a list of clients with the last sale date.
  Easy right....
  Select the columns for the client and then for sale date do a subquery that limits the return to 1 item.</p>
  
  <pre><code>SELECT 
  [Client].[LastName] as C1,
  [Client].[FirstName] as C2,
  (SELECT TOP 1 Transactions.SaleDate FROM Transactions WHERE Transactions.ClientID=Client.ClientID  ORDER BY Transactions.SaleDate Desc) as C3,
  [Client].[ClientID] as C4
  FROM [Client]
  </code></pre>
  
  <p>BUT access is telling me that the subquery has this problem:
  "At most one record can be returned by this subquery."</p>
  
  <p>Ah... but stack overflow has an answer to the 
  "At most one record can be returned by this subquery." error.</p>
  
  <p>--->"Your subquery is returning more than one result."
  "Try fixing the data using select top 1"</p>
  
  <p><a href="https://stackoverflow.com/questions/21529485/at-most-one-record-can-be-returned-by-this-subquery-error-3354">At most one record can be returned by this subquery. (Error 3354)</a></p>
  
  <p>well... Great.</p>
  
  <p>Is there something else in the SQL that could be confusing it that I am totally missing?</p>