count only once per group

[bbxrider]

I thought this would be easy but all the count functions, and IIF etc, seem to count all the records per group. I need to count only once per group.
here's the recs being fed to report from a query, note the 'USE CODE Y/N' will always be same for each rec for a given ID. I'm using a group by ID, so I list all the codes per group ID and NAME, in the detail section

ID	NAME	CODE	USE CODE Y/N
1a	name 1	a	y
1a	name 1	b	y
1a	name 1	c	y
1b	name 2	d	n
1b	name 2	e	n
1b	name 2	f	n
1c	name 3	g	y
1c	name 3	h	y
1c	name 3	i	y
1d	name 4	j	n
1d	name 4	j	n

I can get all the counts of codes, codes per ID, etc but need to get a count
of how many ID's use codes vs not using codes
so for this group of recs it would be:
Total ID's using Codes = 2


Total ID's not using Codes = 2
I've been looking for something like to identify the first or last rec of a group, that would enable me to do a SUM based on the 'USE CODE Y/N' value with a SUM(IFF..
but can't seem to find the way to do this

[June7]

Need 2 queries.

Query1
SELECT ID, [Name], [USE CODE] FROM table GROUP BY ID, [Name], [USE CODE];
or
SELECT DISTINCT ID, [Name], [USE CODE] FROM table;

Query2
SELECT [Use Code], Count(ID) AS CountID FROM Query1 GROUP BY [Use Code];

Or nested subquery.
SELECT Query1.[Use Code], Count(Query1.ID) AS CountID FROM (SELECT ID, [Name], [USE CODE] FROM table GROUP BY ID, [Name], [USE CODE]) AS Query1 GROUP BY Query1.[Use Code];


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention. Also, Name is a reserved word and should avoid reserved words as names.

[bbxrider]

I need to have ID, NAME, and CODE, in the query output, not seeing that in your example. The report group header lists the ID and NAME, then the detail section lists all the CODE(s) for that group, the layout I listed is actually a query output from 2 tables. I see how the distinct works if I only have ID, NAME, and USE_CODE_Y_N (changed name with underscores for readability) but having to combine it with all the CODES for that group is still my bottleneck

[June7]

Okay, then in the final query can have First([Name]) and First(ID).

Including Code field in the aggregate queries does not make sense. Would have to join the final aggregate query back to the original table, linking on the ID.