Question converting SQL Code to Access (Max Distinct) 1

[HRISUser]

Hi All,
I'm new to using Access as a program to run queries. I used to use our old HRIS software, but now am forced to run queries through Access. So I have a table of Data which contains employees where each employee can have one row or as many as 20 rows of data. Each row of data contains a different skill. I am trying to combine the rows into columns. Using my old HRIS program I used to select query only (a button within the program) and then type the command MAX DISTINCT (), however Access doesn't recognize this function. Then I tried to perform a CAST, but can't get MS Access to understand what I'm typing. I have posted my command below. Any help would be GREATLY appreciated.


SELECT DISTINCTROW [EMPLOYEE], [NAME], [SOURCE],
IIf(

="BHI OVERAL",[PROFICLEVEL],"") AS ['Quick Select Rating'], 
IIf([CODE]="BHI OVERAL",[PER-RATING],"") AS ['Quick Select Score'], 
IIf([CODE]="BHI PRB SV",[PER-RATING],"") AS ['Problem Solving'], 
IIf([CODE]="BHI CMT QT",[PER-RATING],"") AS ['Commitment To Quality'], 
IIf([CODE]="BHI EF FLXT",[PER-RATING],"") AS ['Effort & Flexibility'], 
IIf([CODE]="BHI COMM",[PER-RATING],"") AS ['Communication'], 
IIf([CODE]="BHI LEADER",[PER-RATING],"") AS ['Leadership'], 
IIf([CODE]="BHI CST SV",[PER-RATING],"") AS ['Customer Service'], 
IIf([CODE]="MIT TS WK1",[PER-RATING],"") AS ['1st Week Training Score'], 
IIf([CODE]="MIT TS WK2",[PER-RATING],"") AS ['2nd Week Training Score'], 
IIf([CODE]="MIT TS WK3",[PER-RATING],"") AS ['3rd Week Training Score'], 
IIf([CODE]="MIT TS WK4",[PER-RATING],"") AS ['4th Week Training Score'], 
IIf([CODE]="MIT TS WK5",[PER-RATING],"") AS ['5th Week Training Score'], 
IIf([CODE]="MIT TS WK6",[PER-RATING],"") AS ['6th Week Training Score'], 
IIf([CODE]="MIT ASSMT",[PER-RATING],"") AS ['MIT Assessment'], 
IIf([CODE]="MIT RVW BD",[PER-RATING],"") AS ['Review Board Score']
FROM skills;

 

[MajP]

Take a look at the Acess cross tab queries they should do it. The wizard for cross tabs works pretty well. You can take row data and convert it to columns.

 

[cmmrfrds]

SELECT [EMPLOYEE], [NAME], [SOURCE],
max(IIf(

="BHI OVERAL",[PROFICLEVEL],"")) AS ['Quick Select Rating'], 
 max(IIf([CODE]="BHI OVERAL",[PER-RATING],"")) AS ['Quick Select Score'], 
 max(IIf([CODE]="BHI PRB SV",[PER-RATING],"")) AS ['Problem Solving'], 
 max(IIf([CODE]="BHI CMT QT",[PER-RATING],"")) AS ['Commitment To Quality'],
etc............ 
FROM skills
Group by [EMPLOYEE], [NAME], [SOURCE];

 

[HRISUser]

Thank you both for your help with this. I do appreciate it!