--	Get the details for each assessment (CAFAS)
SELECT tblClientInfo.id1, tblClientInfo.cLastName, tblClientInfo.cFirstName, tblAssessDetails.Assessment_Date, 
  tblAssessDetails.Assessment_Administration_Type, tblSubscaleInfo.School_Subscale_Score, tblSubscaleInfo.Home_Subscale_Score, 
  tblSubscaleInfo.Community_Subscale_Score, tblSubscaleInfo.Behavior_Toward_Others_Subscale_Score, tblSubscaleInfo.Mood_Subscale_Score, 
  tblSubscaleInfo.SelfHarmful_Subscale_Score, tblSubscaleInfo.Substance_Use_Subscale_Score, tblSubscaleInfo.Thinking_Subscale_Score
FROM (tblSubscaleInfo INNER JOIN tblAssessDetails ON tblSubscaleInfo.assessmentID = tblAssessDetails.assessmentID)
  INNER JOIN tblClientInfo ON tblAssessDetails.id1 = tblClientInfo.id1;



--	Get the most recent assessment for each client (CAFAS)
SELECT details.id1, details.Assessment_Date, details.Assessment_Date, 
	     details.Assessment_Administration_Type, tblSubscaleInfo.School_Subscale_Score, 
	     tblSubscaleInfo.Home_Subscale_Score, tblSubscaleInfo.Community_Subscale_Score, 
	     tblSubscaleInfo.Behavior_Toward_Others_Subscale_Score, tblSubscaleInfo.Mood_Subscale_Score, 
	     tblSubscaleInfo.SelfHarmful_Subscale_Score, tblSubscaleInfo.Substance_Use_Subscale_Score, 
	     tblSubscaleInfo.Thinking_Subscale_Score
FROM (tblAssessDetails AS details INNER JOIN (SELECT tblAssessDetails.id1, MAX(tblAssessDetails.Assessment_Date) AS maxDate 
											  FROM dbo.tblAssessDetails 
											  GROUP BY tblAssessDetails.id1)  AS maxAssessment 
	 ON (details.Assessment_Date=maxAssessment.maxDate) AND (details.id1=maxAssessment.id1)) 
INNER JOIN tblSubscaleInfo ON details.assessmentID=tblSubscaleInfo.assessmentID
ORDER BY details.id1;