access query if both order types match for employee

[Colleen2]

trying to write a query to return "yay" or "Nay" if both attainment = yes for each Rep_Empid

[ranman256]

if Boolean field
select * from table where [Attain1]=true and [Attain2]=true

if text:

select * from table where [Attain1]='yes' and [Attain2]='yes'

[Colleen2]

this is in a query and that didn't work
Sample:

[Colleen2]

just returns 1 for those that are yes and 0 for no
IIf([rep_empid],[attainment]='yes' And [attainment]='yes')

Rep_EmpID	SA_ID	Sales Assignment Employee Name	Order Type	Attainment
3477	SA01686	Reed,Lana L	Digital	No
3477	SA01686	Reed,Lana L	TotalRevenue	YES
5831	SA01068	Fields-Snyder,Terry Ann	Digital	YES
5831	SA01068	Fields-Snyder,Terry Ann	TotalRevenue	No
16300	SA00356	Jordan,Mary M	Digital	YES
16300	SA00356	Jordan,Mary M	TotalRevenue	YES

[davegri]

That little comma should be a period and use plain IF

If ([rep_empid].[attainment]='yes' And [attainment]='yes')

Would be helpful to see the complete query so context implications would be plainer.

[CarlettoFed]

What you wrote does not make sense, it shows the Sql predicate of the query.

[davegri]

What you wrote does not make sense, it shows the Sql predicate of the query.

I think more clearly, your requirement is that you have a table with 2 records for an employee. You want a query to show the employees with 'Yes' in the attainment field for both records.

[NoellaG]

create 2 subqueries: 1 selects the rep_empID, SA_ID, employee_name where order type = 'digital' and attainment = 'Yes'; the other one selects the rep_empID, SA_ID, employee_name where order type = 'TotalRevenue and attainment = 'Yes'.
Join the 2 queries together on the field rep_empID (in T-SQL you use an OUTER APPLY for that, but I remember that Access SQL can differ from T-SQL) and the resulting recordset should only contain those employees where both attainment fields are true.

[Colleen2]

that got me closer but for the ones that both = Yes should be Yay not Nay like Mary Jordan
her is the statement Expr1: IIf([rep_empid].[attainment]='yes' And [attainment]='yes',"Yes","Nay")

[Colleen2]

I don't want to remove any employees just identify the ones that have yes for both order types "Digital" and "TotalRevenue"

[Colleen2]

Yes that is correct

[Colleen2]

Yes that is correct I have a table with 2 records for an employee. I want a query to show the employees with 'Yes' in the attainment field for both records

[CarlettoFed]

When asking questions you need to be clear from the start.
Assuming that the table, where the data you exposed in post # 4 is,

Rep_EmpID	SA_ID	Sales Assignment Employee Name	Order Type	Attainment
3477	SA01686	Reed,Lana L	Digital	No
3477	SA01686	Reed,Lana L	TotalRevenue	YES
5831	SA01068	Fields-Snyder,Terry Ann	Digital	YES
5831	SA01068	Fields-Snyder,Terry Ann	TotalRevenue	No
16300	SA00356	Jordan,Mary M	Digital	YES
16300	SA00356	Jordan,Mary M	TotalRevenue	YES

is called Rep_Emps the query is the following:

SELECT Rep_Emps.*, IIf([CountOfRep_EmpID]=1,"Nay","Yes") AS Outcome
FROM Rep_Emps INNER JOIN (SELECT Rep_Emps.Rep_EmpID, Count(Rep_Emps.Rep_EmpID) AS CountOfRep_EmpID
FROM Rep_Emps
WHERE (((Rep_Emps.[Order Type])="Digital" Or (Rep_Emps.[Order Type])="TotalRevenue") AND ((Rep_Emps.Attainment)="yes"))
GROUP BY Rep_Emps.Rep_EmpID) AS SearchData
 ON Rep_Emps.Rep_EmpID = SearchData.Rep_EmpID;

and the result is the following

Rep_EmpID	SA_ID	Sales Assignment Employee Name	Order Type	Attainment	Outcome
3477	SA01686	Reed,Lana L	Digital	No	Nay
3477	SA01686	Reed,Lana L	TotalRevenue	YES	Nay
5831	SA01068	Fields-Snyder,Terry Ann	Digital	YES	Nay
5831	SA01068	Fields-Snyder,Terry Ann	TotalRevenue	No	Nay
16300	SA00356	Jordan,Mary M	Digital	YES	Yes
16300	SA00356	Jordan,Mary M	TotalRevenue	YES	Yes

[Colleen2]

I apologize for not being clear from the start. Yes this is exactly what I wanted. How do I put it in an expression?

[Colleen2]

I figured it out! thank you all for helping me.