How To Select Top 1 In Access Query - And Actually Get It To Work

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 For instance - these two: Access join on

Solution 1:

Add that as a JOIN and try it, Access sql parsing may be busted (been there), try this:

SELECT 
    c.[LastName] as C1,
    c.[FirstName] as C2,
    sd.maxsaledate as C3,
    c.[ClientID] as C4
FROM 
    [Client] c
    left join (
      select clientid, max(SaleDate) as maxsaledate from transactions groupby clientid
    ) sd on
    c.ClientID = sd.ClientID

Solution 2:

Actually, if you want the top 1 to work and FORCE only one record, then add a order by on a column that is unique.

So this will work.

SELECT 
 [Client].[LastName] as C1,
 [Client].[FirstName] as C2,
(SELECT TOP 1 Transactions.SaleDate FROM Transactions WHERE
Transactions.ClientID=Client.ClientID  ORDERBY Transactions.SaleDate Desc,
  ID) as C3,
  [Client].[ClientID] as C4
  FROM [Client]

So the simple addition of a column in the orderby of the a unique column (autonumber id) will thus always result in only one row. And the query as you have likely will perform better then using max().