Hi
First of all, with the data you supplied the results should be:
DialysisInf_ID |
Pt_ID (patient id) |
Info_Date |
DialysisMod_ID (number) |
1 |
5 |
23/11/2017 |
68 |
2 |
6 |
12/10/2018 |
68 |
There are several ways of getting these results.
One way is to use two queries:
Query1: used to get the most recent date for each Pt_ID
Code:
SELECT tbl_DialysisInfo.[Pt_ID (patient id)], Max(tbl_DialysisInfo.Info_Date) AS MaxOfInfo_DateFROM tbl_DialysisInfo
GROUP BY tbl_DialysisInfo.[Pt_ID (patient id)];
Query2: join this to the original table to get all 4 fields for those 2 records
Code:
SELECT tbl_DialysisInfo.*
FROM tbl_DialysisInfo INNER JOIN Query1 ON (tbl_DialysisInfo.Info_Date = Query1.MaxOfInfo_Date) AND (tbl_DialysisInfo.[Pt_ID (patient id)] = Query1.[Pt_ID (patient id)]);
There are other ways but this may be the easiest to understand
EDIT: Paul replied whilst I was typing - same solution