SELECT(DISINCT) in MS Access

Question

I couldn't find the solution to the problem I have in other SELECT(DISINCT) in MS Access-type questions and I've been struggling with it for a good few hours;

I've got a table Photos with two columns: PhotoTypeId and PatientId. Every patient can have multiple types of photos made multiple times.

Example:

PhotoTypeId,PatientId  
13,1050  
14,1050  
13,1050  
13,1051  
13,1054

Description:
Patient #1050 had photos of type 13 and 14 taken, whereas patients #1051 and #1054 had only those of type 13.

What I want to find is the type of photo that was made to the most distinct patients. In the example above, it's photo type 13, cause it was made to 3 distinct patients (#1050 twice, #1051, #1054)

I have to do it in MS Access and I want to do it using SQL. I tried something like:

SELECT PhotoId, COUNT(DISTINCT PatientId)
FROM Photos
GROUP BY PhotoId;

But MS Access doesn't support the COUNT(DISTINCT x) syntax; how to do it?

Answer 1

MS Access does not support count(disitnct). But you can do:

select top 1 photoid, count(*)
from (select distinct photoid, patientid
      from photos
     ) as pp
group by photoid
order by count(*) desc;

MS Access treats top 1 as top 1 with ties. If you want only one row, then change the order by:

select top 1 photoid, count(*)
from (select distinct photoid, patientid
      from photos
     ) as pp
group by photoid
order by count(*) desc, photoid;

Answer 2

Use sub-query as MS access doesn't support that syntax :

select top 1 p.PhotoId, count(*)
from (select distinct PhotoId, PatientId from Photos
     ) as p
group by p.PhotoId
order by count(*) desc;