I've got two tables, Account and Contact inner join on AccountID
The Contact table has a JobFunction column populated with CEO or CFO or CTO.
Some AccountIDs may have multiple CEO, CTO or CTOs
I loaded the data with
if (not(JobFunction)='CEO', 1)as [Has CEO],
if (not(JobFunction)='CTO', 1)as [Has CTO],
if (not(JobFunction)='CFO', 1)as [Has CFO]
I need to identify the AccountIDs that do not have CEO CTO CFO
My Table columns are AccountID | Has CEO | Has CFO | Has CTO
The result I get is multiple rows of data for each AccountID
Id like to return one row per AccountID with "Yes" or "No" in the Has CEO Has CFO Has CTO columns
I also tried creating the Has CEO Has CFO Has CTO columns with a formula .. Something like if the sum of the AccountID row is zero, return "No" or else return "Yes". But, this only returns "Yes" for everything
=if(sum([Has CEO] <>0), 'Yes','No')
