Hello finally got the following query to work! Weirdly it only works in Access 2007 as opposed to Access 2003 which tries to 'optimise' the code and breaks it!
Code:
SELECT tQ.[PIN], tQ.[P__Business_Unit], tQ.[EPISODEUNIQUEKEY], tQ.[D_S__Episode_Start_Date], tQ.[D_S__Finish_Date], tQ.[S__Episode_Type], tQ.[CLINICALENCOUNTERUNIQUEKEY], tQ.[D_S__Attendance_Date], tQ.[S__Event_Type], tQ.[S__Seen_By], tQ.[S__Outcome]
FROM (SELECT
[2-5 All Man Report Events within Man Referral Episodes].[PIN],
[2-5 All Man Report Events within Man Referral Episodes].[P__Business_Unit],
[2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],
[2-5 All Man Report Events within Man Referral Episodes].[D_S__Episode_Start_Date],
[2-5 All Man Report Events within Man Referral Episodes].[D_S__Finish_Date],
[2-5 All Man Report Events within Man Referral Episodes].[S__Episode_Type],
[2-5 All Man Report Events within Man Referral Episodes].[CLINICALENCOUNTERUNIQUEKEY],
[2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date],
[2-5 All Man Report Events within Man Referral Episodes].[S__Event_Type],
[2-5 All Man Report Events within Man Referral Episodes].[S__Seen_By],
[2-5 All Man Report Events within Man Referral Episodes].[S__Outcome]
FROM
[2-5 All Man Report Events within Man Referral Episodes]
INNER JOIN
(
SELECT
[2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY],
Min([2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date]) AS nEventDate
FROM
[2-5 All Man Report Events within Man Referral Episodes]
GROUP BY
[2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY]
) As tmp
ON
[2-5 All Man Report Events within Man Referral Episodes].[EPISODEUNIQUEKEY] = tmp.[EPISODEUNIQUEKEY]
AND
[2-5 All Man Report Events within Man Referral Episodes].[D_S__Attendance_Date] = tmp.[nEventDate]
) AS tQ;