Access query with "last" and display the rest too I have an Access DB about measuring tools.

I have 2 tables: tblTools, which countains the details of the device, the tblCalibration contains when each device was calibrated.

tblTools: tool_ID; tool_Name 
tblCalibration: calibration_ID; tool_ID ;calibration_Date


I wrote a query to get the last calibration date, but it only shows the devices which have a calibration date.  I need the results to show all devices, not just those that have been calibrated.

Is there any solution for a query that will list all of the devices plus the last calibration date if one exists?
This query selects the LAST calibration date for each tool.

SELECT t.tool_Name
    ,(
        SELECT TOP 1 c.calibration_Date
        FROM tblCalibration c
        WHERE t.tool_ID = c.tool_ID
        ORDER BY c.calibration_Date DESC
        )
FROM tblTools t


If you want to see all the tools and calibration events you have to use LEFT JOIN:

SELECT t.tool_Name
    ,c.calibration_Date
FROM tblTools t
LEFT JOIN tblCalibration c ON t.tool_ID = c.tool_ID
ORDER BY t.tool_Name, c.calibration_Date DESC --ORDER BY is optional


The first query returns all the tools and the date of last calibration of this tool. If you have 100 tools, you get 100 records, no matter how many times you calibrated each one. The second query will return all the tools and all the calibration events. It returns all calibrations for the tool instead of the last one.