Help with iif statement

[mslenker]

Every time I run this query access tells me Iff statement isn't part of aggregate function not sure what to do?

SELECT Employees.[SS #], Employees.Status, Employees.[Senior Date], Employees.[Last Name]+", "+Employees.[First Name]+" "+Employees.[Middle Name] AS Employee, iif ([Employee Qualifications].[Family Number] = 1, Min (QryEmployeeQualConvertGrade.Grade), "no") AS Family1
FROM ([Employee Qualifications] INNER JOIN Employees ON [Employee Qualifications].[SS #]=Employees.[SS #]) INNER JOIN Families ON [Employee Qualifications].[Family Number]=Families.[Family Number]
GROUP BY Employees.[SS #], Employees.Status, Employees.[Senior Date], Employees.[Last Name]+", "+Employees.[First Name]+" "+Employees.[Middle Name];

 

[pr2-eugin]

Hello, Welcome to AWF..

Try this..

[COLOR=Silver]SELECT Employees.[SS #], Employees.Status, Employees.[Senior Date], Employees.[Last Name] & ", " & Employees.[First Name] & " " & Employees.[Middle Name] AS Employee, IIf ([Employee Qualifications].[Family Number] = 1, "Yes", "No") AS YesNoField
FROM ([Employee Qualifications] INNER JOIN Employees ON [Employee Qualifications].[SS #]=Employees.[SS #]) INNER JOIN Families ON [Employee Qualifications].[Family Number]=Families.[Family Number]
GROUP BY Employees.[SS #], Employees.Status, Employee; [/COLOR]

 

[Brianwarnock]

Well it obviously isn't , but the question I would ask is why are you running an aggregate (aka) totals query when it looks like every record will be unique and therefore no grouping will take place.

Brian

 

[mslenker]

I've just started the statement there are many other families and each employee has more than one family they are qualified in. So I need to bring each employees family together in one query to create a report. I figured I could group on social, but it is still giving me the same employee multiple times.

 

[jdraw]

I suggest you try to describe in plain English WHAT it is that you are trying to do. If you describe it in clear distinct steps, it will be easier for the reader to offer assistance/suggestions. And it may help clarify your approach which seems to be more of a guess at present.

And the IFF statement is really IIF, but we've all done that at least once.

Good luck with your project.