Is it possible to set an MS Access Query on the attached table to get only the records with 'Status' = "CLOSE" on maximium level of 'Rev'?

I'm working on an MS Access database and can't find the right SQL syntax for the query to get as result only the records with 'Status' = "CLOSE" on the maximum level of 'Rev'. The query should show the following result:

IDBug	MaxDiRev	Status
2	2	CLOSE
3	5	CLOSE

Following table 'tblCronologiaB_0' in text-format:

ID	IDBug	Rev	Status	DateB
1	1	1	OPEN	01/06/2023
2	1	1	SENT	01/06/2023
3	1	2	CHECK	05/06/2023
4	1	2	OPEN	06/06/2023
5	1	2	SENT	06/06/2023
6	1	3	CHECK	16/06/2023
7	1	3	CLOSE	17/06/2023
23	1	4	OPEN	10/07/2023
24	1	4	SENT	11/07/2023
25	1	5	CHECK	20/07/2023
26	1	5	OPEN	21/07/2023
8	2	1	CHECK	06/06/2023
9	2	1	OPEN	07/06/2023
10	2	1	SENT	07/06/2023
11	2	2	CHECK	17/06/2023
12	2	2	CLOSE	18/06/2023
13	3	1	OPEN	11/07/2023
14	3	1	SENT	11/07/2023
15	3	2	CHECK	15/07/2023
16	3	2	OPEN	16/07/2023
17	3	2	SENT	16/07/2023
18	3	3	CHECK	26/07/2023
19	3	3	OPEN	27/07/2023
20	3	4	SENT	28/07/2023
21	3	5	CHECK	10/08/2023
22	3	5	CLOSE	12/08/2023
27	4	1	OPEN	12/07/2023
30	4	1	SENT	14/07/2023
28	5	1	OPEN	12/07/2023
31	5	1	SENT	14/07/2023
29	6	1	OPEN	13/07/2023
32	6	1	SENT	14/07/2023

Using the following SQL statement query, I get as result also IDBug = 1 with MaxDiRev = 3, because Status is "CLOSE", even if 3 is not the maximum of Rev filed for IDBug = 1:

SELECT tblCronologiaB_0.IDBug, Max(tblCronologiaB_0.Rev) AS MaxDiRev, tblCronologiaB_0.Status
FROM tblCronologiaB_0
GROUP BY tblCronologiaB_0.IDBug, tblCronologiaB_0.Status
HAVING (((tblCronologiaB_0.Status)="CLOSE"));

Can someone please help on this? Many thanks

Solution

Try a correlated subquery using TOP N. Only IDBug 2, 3 will return.

SELECT tblCronologiaB_0.* FROM tblCronologiaB_0 
WHERE ID IN(
    SELECT TOP 1 ID FROM tblCronologiaB_0 AS Dupe 
    WHERE Dupe.IDBug = tblCronologiaB_0.IDBug ORDER BY Dupe.ID DESC) 
AND Status="CLOSE";