Query Help

[Danny_H]

Hi All

I am trying to show in a query the completion percentage of each project but am struggling to show this. There is 2 sets of criteria that determine if a record within a project is complete, Yes or No. What I would like to show is against each project how many records are yes and where it is no display this as a zero. I have looked at the Nz function but cant seem to get this to work

Is it something else that I should be using?

SQL code below:

SELECT Qry_Data.Project_Name, Qry_Data.Tranche_No, Qry_Data.New_Source, Count(Qry_Data.Partnumber) AS Partnumbers, Qry_Data.FAIRApproved
FROM Qry_Data
GROUP BY Qry_Data.Project_Name, Qry_Data.Tranche_No, Qry_Data.New_Source, Qry_Data.FAIRApproved
HAVING (((Qry_Data.FAIRApproved)="Yes"))
ORDER BY Qry_Data.Tranche_No;

Thanks

 

[pr2-eugin]

Try this code,

SELECT Qry_Data.Project_Name, Qry_Data.Tranche_No, Qry_Data.New_Source, Sum(IIF(Qry_Data.FAIRApproved = True, 1, 0)) AS Completed, Sum(IIF(Qry_Data.FAIRApproved = False, 1, 0)) As NotCompleted
FROM Qry_Data
GROUP BY Qry_Data.Project_Name, Qry_Data.Tranche_No, Qry_Data.New_Source
ORDER BY Qry_Data.Tranche_No;

 

[Danny_H]

Thanks Paul

That worked spot on

 

[pr2-eugin]

Glad to help ! Good Luck