Distinct count

[thart21]

Tried several suggestions in the forum but haven't found one to match my needs.

I have one table, Jupiter and need to use this query

SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind
FROM Jupiter
GROUP BY Jupiter.codename, Jupiter.tooling_ind
HAVING (((Jupiter.tooling_ind)="Y"));

For each "codename", there is an attached "part_no" which can appear under a codename several times. The problem is that I need to count only one instance of the "part_no" for each "codename".
So, my query looks like this:
Build # Needing Tool #Tooled
LP1 769 192

The # Needing Tool is counting every record that, say pn 10592 shows up in and I need it to be counted only once in each "Build"

I did try this suggestion I found with no results.

SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind
FROM (SELECT DISTINCT Jupiter.part_no FROM Jupiter)Jupiter
GROUP BY Jupiter.codename, Jupiter.tooling_ind
HAVING (((Jupiter.tooling_ind)="Y"));

Is there a way to do this somehow?

Thanks!

Toni

 

[Newman]

I don't know if that would fix your problem since I didn't understand it, but I noticed that you give the alias «Build» to «Codename» but you grouped by «Codename» instead of «Build».

 

[thart21]

The grouping by "codename" is what Access did automatically from the query grid and that is working fine. I'm trying to eliminate duplicate part numbers from the query. I think I am going to need to create a new table using "DISTINCT" to eliminate duplicate part numbers within each "codename" unless there is in fact a way to do it within a query.

 

[Newman]

Not sure, but if you used two queries instead of one, the first grouping by «codename» and «part number» without duplicate, and the second using the first one to do the counts...