Query: Two tables With Yes/No Filed

[Judasdac]

Hi All,



I'm a bit of a newbie to Access and have been struggling with this problem all night.

I have two tables: one is a list of employees with the unique ID being employee ID. The other table is just a list of employee IDs and is only for those employees who are receiving a bonus this year.

I have created a field called "bonus" in the main table and would it like it to display "yes" if the person is getting a bonus and "no" if they are not.

I have tried to do a make table query and use an iif statement in the bonus field, but I can't quite figure it out. The query keeps resulting in a table that shows only the employees that are getting a bonus.

I'm using an iif statement in the bonus field in query design view:

IIf([Employee]![Employee ID]=[Bonus![Employee ID],"yes","no")

As I said this just results in a query with only the employees that are getting bonuses rather than a field with all employees and a "yes" or "no"

This was pretty easy for me to do in Excel; I just wrote a Vlookup and it worked perfectly. I can't quite figure out in Access though.

Any ideas?

Thanks in advance for the assistance!

[alansidman]

How have you joined the two tables in your make table query. Post your SQL statment for this query.

Alan

[Judasdac]

I'm not sure I can't understand how to write the SQL statement. I have been using the query design wizard and the expression builder in design mode.

[alansidman]

Open your query in design view. In the upper left hand corner, there is an icon that looks like a datasheet. Click on the arrow next to it. It will open a drop down. Select SQL and the statement that equates to the design view will appear. Access creates it for you. Copy and paste it into a thread for us to review. Looking at it will also help you to understand what is happening with SQL statements created by Access from the design view.

Alan

[Judasdac]

Thank you so much! I just learned something new

This is the SQL statement:

SELECT Employees.[Employee ID] AS [Employee_Employee ID], Employees.[First name], , Employees.[Bonus], [Bonus].[Employee ID] AS [Bonus_Employee ID]
FROM Employees LEFT JOIN [Bonus] ON Employees.[Employee ID] = [Bonus].[Employee ID];

[alansidman]

I recreated your situation as best as I understood it. Attached is the sample db.

Is this what you are looking for?

Alan

[Judasdac]

This is very helpful! Thank you so much.

Cheers