I have the following table in MS Access :

```
FID FNAME                 SID   FBOOL
14  A11111_1111111_1.png    5   false
15  A11111_1111111_15.png   5   true
16  A11111_1111111_16.png   5   false
17  A11111_1111111_17.png   5   false
18  A1123_2345_1.png        7   false
19  A1123_2345_19.png       7   false
20  A1123_2345_20.png       7   false
 3  A111_111_1.bmp          8   true
10  A111_111_4.bmp          8   true
21  A111_111_13.png         8   false
31  A1123_2345_1.png        9   false
32  A1123_2345_19.png       9   false
33  A1123_2345_20.png       9                 <=EMPTY Value
34  A1123_2345_1.png        10                <=EMPTY Value
35  A1123_2345_19.png       10                <=EMPTY Value
36  A1123_2345_20.png       10                <=EMPTY Value
44  A1123_2345_20.png       11  true          
45  A1123_2345_1.png        11  false       
46  A1123_2345_19.png       11                <=EMPTY Value
47  A1123_2345_20.png       11  true

```

I would like to extract using SQL the FID in the following way:

```
15  A11111_1111111_15.png   5   true
20  A1123_2345_20.png       7   false
10  A111_111_4.bmp          8   true
33  A1123_2345_20.png       9                 <=EMPTY Value
36  A1123_2345_20.png       10                <=EMPTY Value
47  A1123_2345_20.png       11  true     

```

This will be a subquery, so I can't use UNION. (I tried with UNION, but it gives: "this operation is not allowed in subqueries")

The header of SirData is:

```
SID PNumber SNumber STitle  STime   SNote  //SID is the primary key

```

The header of SirFoto is:

```
FID FNAME  SID   FBOOL  //the SID is a foreign key, FID is primary key

```

My query looks like this:

```
SELECT SirData.SID, SirData.PNumber, SirData.SNumber, SirData.STitle, SirData.STime, SirData.SNote, FOTO.FID, FOTO.FNAME FROM SirData LEFT JOIN ( Select SID, FID, FNAME FROM SirFoto where FID IN  (  Select MAX(FID) from SirFoto group by SID  order by MAX(FBOOL) desc, MAX(FID) desc   //I tried like this, but is incorrect !)  )  FOTO ON SirData.SID = FOTO.SID where SirData.PNumber=\  selectedprc  \   order by SirData.PNumber ASC, SirData.SNumber ASC

```