Originally Posted by
CJ_London
you need an aliased query to limit the record to the last date
SELECT cm_server_drives.drive_letter AS Drive, cm_server_drives.drive_size AS [Size], cm_server_drives.size_id AS Unit, cm_server_drives.row_create_ts AS [Created on], cm_server_drives.row_create_by AS [Created by], cm_server_drives.server_name
FROM (cm_server_drives INNER JOIN cm_sizes ON cm_server_drives.size_id = cm_sizes.size_id) INNER JOIN (SELECT drive_letter, Max(row_create_ts) AS MaxDate FROM cm_server_drives GROUP BY drive_letter) LD ON cm_server_drives.drive_letter=LD.drive_letter AND cm_server_drives.row_create_ts=LD.MaxDate
WHERE (((cm_server_drives.server_name)=[Forms]![frm_servers]![Server]))
ORDER BY cm_server_drives.drive_letter, cm_server_drives.row_create_ts DESC