Access SQL - count unique value

Question

SQL Columns:

Fishingcourse       Supervisor      Lake
AAAA                 Martha         1
BBBB                 Josh           2
CCCC                 Evelyn         1
AAAA                 Josh           1
AAAA                 Paul           2

How can I find fishingcourses that has been held at at least 2 lakes? Not including duplicates.

Answer 1

One way is to compare the minimum and maximum values:

select fishingcourse
from t
group by fishingcourse
having min(lake) <> max(lake);