Access - Select distinct records where only one column is distinct

Question

I have an Access table with two columns (ID and Active) with data like the following:

ID  |  Active
------------
123 | 0
124 | 0
125 | 0
123 | 1
314 | 1
948 | 1

I want to select the distinct records that have a unique ID (that only exist once, not just the first time they exist), but I also need the Active value. If I do a

SELECT DISTINCT ID from table1

I get the unique IDs, but not the sheet. It also returns 123 which isn't unique in the table. If I do:

SELECT DISTINCT * from table1

I get duplicate IDs if they have different Active values. I need a query to get the unique IDs and their associated Sheet value. It would return the following:

ID  |  Active
------------
124 | 0
125 | 0
314 | 1
948 | 1

Do I need to put these into two different tables and do an outer join? Active is only ever 0 or 1.

Answer 1

Use this:

SELECT *
FROM table1
WHERE Id IN (SELECT Id FROM table1 GROUP BY Id HAVING COUNT(Id)=1)