Adding values to cols based on criteria

[kbsudhir]

Hi All,

I am trying to map certifications done by colleagues in my department.
There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.

Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.

Requesting guidance how to create this query.

Regards
Sudhir

 

[Uncle Gizmo]

I'm not getting a clear picture of your current situation and how you want to change it?

 

[kbsudhir]

I have two tables
1. Master Personnel Table
2. Certification Details

We have to four certifications.
I am generating 4 queries one for each certification. This provides me the list of people who have certified in each of them from certification details table.

Now I want to create a query where I want to add 4 columns based on certification name ex. A, B, C & D along with all the columns of master personnel table. The new columns will have values Yes or No based of the emp no available in each of the 4 queries create above.

I am not changing the master personnel table in any way. only adding new columns in the query so that I know which employees are certified in which programs directly running this query.

I am trying to use IIF condition in expression builder but its throwing some duplicates.
I am just using the master table and one certification query.

Below is the query which I am trying.

SELECT DISTINCT DataSheet.[Emp Name], DataSheet.[Emp No], DataSheet.[Emp Mail ID], DataSheet.[DU Head for Emp DU], DataSheet.[DU Head Name for Emp DU], DataSheet.[Allocated City], DataSheet.[Reporting To Emp No], DataSheet.[Reporting To Emp Name], DataSheet.[Reporting To Mail Id], DataSheet.[Project DM], DataSheet.[Project DM MailID], DataSheet.[Project DU Head], DataSheet.[Project DUHead Name], DataSheet.[Project SDM], DataSheet.[Project SDM MailId], IIf([DataSheet]![Emp No]=[Foundation_Certified_In_ALCON]![Attendee*EmpNo],"Yes","No") AS FND_Certified
FROM DataSheet, Foundation_Certified_In_ALCON;

Regards
Sudhir

 

[namliam]

Have you tried using a cross tab query to get what you need/want?

 

[kbsudhir]

Ok.. wil try that too