The below process is designed to pick out only AsbestosUPRNs and get the lowest OverallRiskCategory where they have the newest SurveyDate. I just wanted to ensure this is the best way, it seems neat now though as well as second code, which in reality is the same thing, based on two tables.
SELECT AsbestosUPRN, MIN(OverallRiskCategory) AS OverallRiskCategory, MAX(SurveyDate) AS SurveyDate FROM TblAsbestos GROUP BY AsbestosUPRN
Two Table version:
SELECT p.UCARN, MAX(a.OverallRiskCatNumberical) as OverallRiskCatNumberical, MAX(a.SurveyDate) AS SurveyDate FROM TblAsbestos AS a INNER JOIN TblProperty AS p ON p.UPRN = a.AsbestosUPRN GROUP BY p.UCARN