Select Distinct not working the way I need it to

[shanice]

Hello!

I have a query that lists the roles that staff play on each project. In many cases staff played more than one role on a project, so staff names appear multiple times. I.e.:
Row 1 - Name: Sue Smith - Role: Editor - Project: CC1
Row 2 - Name: Sue Smith - Role: Analyst - Project CC1
Row 3 - Name Sue Smith - Role: Editor - Project ZZ2

I want to count the total number of unique projects that each staff worked on. So in Sue Smith's case I would like to see:

Row 1 - Name: Sue Smith - Total Number of Projects: 2

I'm using the following sql statement:

SELECT DISTINCT Count(Roles.ProposalName) AS CountOfProposalName, Roles.Staff
FROM Roles
WHERE (((Roles.ProposalNumber)=(select distinct ProposalNumber
from Proposals
where ProposalNumber = Roles.ProposalNumber)))
GROUP BY Roles.Staff;

I've tried several codes and the output continues to add duplicate projects. It's not showing duplicate names though (which is good). So based on data above the output is:

Row 1 - Name: Sue Smith - Total Number of Projects: 3

Please help...

 

[Taruz]

Hi..

tiry this..:

select staff, count(proposalname) 
         from (select staff, proposalname
                       from roles
                       group by proposalname, staff)  as trz
group by staff