OUTER APPLY to MS ACCESS

Question

How can I transform this code (sql) to ms access:

SELECT *
FROM dbo.Movimiento m
OUTER APPLY ( SELECT TOP 1 
                     costo 
               FROM  dbo.Unitario u
               WHERE u.Cod = m.Cod 
               AND   u.Fecha <= m.Fecha 
               ORDER BY u.Fecha DESC) u 

Answer 1

You can use a correlated subquery:

SELECT m.*,
       (SELECT TOP 1 costo 
        FROM dbo.Unitario as u
        WHERE u.Cod = m.Cod AND
              u.Fecha <= m.Fecha 
        ORDER BY u.Fecha DESC
      ) as costo 
FROM dbo.Movimiento as m;