The following SQL is a query that I've got in my database that pulls up all Candidate records with a minimum education level and # years of experience that is greater than or equal to the same fields in my Position Profiles. Another criterion is that the competency for the related candidate equal at least one of the five competency requirements in my Position Profiles table. The code is below... (don't know if you'll need it to understand...) Here's my problem: When a candidates competencies match more than one of the competency requirements for that position, the record pops up in the query for each instance of a match. If three of the employee's competencies match the requirements for the position, I get three of that record. This is no problem, until I am running a report, based on the query. My question is (after such a long story) is there a way to group the candidate record result so that the report doesn't have all three instances of the match in three different report records? Thank you for your help!
SELECT [Candidate Profile Query].[Full Name], [Candidate Profile Query].[# Years Related Work Experience], [Candidate Profile Query].[Education Level], IIf([Education Level]>=[Min Education Required],1,0) AS CountEd, IIf([# Years Related Work Experience]>=[# Years Related Work Experience],1,0) AS CountEx, [CountEd]+[CountEx] AS [Sum], [Position Comp Scores].[Proficiency 1]+[Years of Use 1]+[Recency of Use 1] AS [Comp 1 Score], [Position Comp Scores].[Proficiency 2]+[Years of Use 2]+[Recency of Use 2] AS [Comp 2 Score], [Position Comp Scores].[Proficiency 3]+[Years of Use 3]+[Recency of Use 3] AS [Comp 3 Score], [Position Comp Scores].[Proficiency 4]+[Years of Use 4]+[Recency of Use 4] AS [Comp 4 Score], [Position Comp Scores].[Proficiency 5]+[Years of Use 5]+[Recency of Use 5] AS [Comp 5 Score], [Position Comp Scores].[Comp Total Score], [Employee Competencies].Competency, [Employee Competencies].[Competency Score], [Position Comp Scores].[Position ID], [Candidate Profile Query].[Dept ID], [Candidate Profile Query].Descr, [Candidate Profile Query].Division, [Candidate Profile Query].[Mgr Level], [Candidate Profile Query].[Yrs with SW], [Candidate Profile Query].[Yrs in Position], [Candidate Profile Query].[Promotion Readiness Date], [Candidate Profile Query].[Promotion Readiness Level], [Candidate Profile Query].[Profile Last Updated], [Candidate Profile Query].[Job Description Available], [Candidate Profile Query].[IDP Status], [Candidate Profile Query].[IDP Last Updated], [Candidate Profile Query].[IDP Due Date], [Candidate Profile Query].[Competencies Last Updated], [Candidate Profile Query].[Rank Level], [Candidate Profile Query].[IDP Path], [Candidate Profile Query].[Resume Path], [Candidate Profile Query].[Job Title], [Candidate Profile Query].[Full Supervisor Name], [Candidate Profile Query].[EE ID], [Candidate Profile Query].DOH, [Candidate Profile Query].Grade, [Position Comp Scores].[Min # Years Related Experience Required], [Position Comp Scores].[Min Education Required], [Position Comp Scores].[Competency Requirement 1], [Position Comp Scores].[Competency Requirement 2], [Position Comp Scores].[Competency Requirement 3], [Position Comp Scores].[Competency Requirement 4], [Position Comp Scores].[Competency Requirement 5]
FROM [Position Comp Scores], [Employee Competencies], [Candidate Profile Query]
WHERE ((([Candidate Profile Query].[# Years Related Work Experience])>=[Position Profiles]![Min # Years Related Experience Required]) AND (([Candidate Profile Query].[Education Level])>=[Position Profiles]![Min Education Required]) AND (([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 1] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 2] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 3] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 4] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 5]) AND (([Position Comp Scores].[Position ID])=[Please Enter Position ID#:]));
KellyJo
SELECT [Candidate Profile Query].[Full Name], [Candidate Profile Query].[# Years Related Work Experience], [Candidate Profile Query].[Education Level], IIf([Education Level]>=[Min Education Required],1,0) AS CountEd, IIf([# Years Related Work Experience]>=[# Years Related Work Experience],1,0) AS CountEx, [CountEd]+[CountEx] AS [Sum], [Position Comp Scores].[Proficiency 1]+[Years of Use 1]+[Recency of Use 1] AS [Comp 1 Score], [Position Comp Scores].[Proficiency 2]+[Years of Use 2]+[Recency of Use 2] AS [Comp 2 Score], [Position Comp Scores].[Proficiency 3]+[Years of Use 3]+[Recency of Use 3] AS [Comp 3 Score], [Position Comp Scores].[Proficiency 4]+[Years of Use 4]+[Recency of Use 4] AS [Comp 4 Score], [Position Comp Scores].[Proficiency 5]+[Years of Use 5]+[Recency of Use 5] AS [Comp 5 Score], [Position Comp Scores].[Comp Total Score], [Employee Competencies].Competency, [Employee Competencies].[Competency Score], [Position Comp Scores].[Position ID], [Candidate Profile Query].[Dept ID], [Candidate Profile Query].Descr, [Candidate Profile Query].Division, [Candidate Profile Query].[Mgr Level], [Candidate Profile Query].[Yrs with SW], [Candidate Profile Query].[Yrs in Position], [Candidate Profile Query].[Promotion Readiness Date], [Candidate Profile Query].[Promotion Readiness Level], [Candidate Profile Query].[Profile Last Updated], [Candidate Profile Query].[Job Description Available], [Candidate Profile Query].[IDP Status], [Candidate Profile Query].[IDP Last Updated], [Candidate Profile Query].[IDP Due Date], [Candidate Profile Query].[Competencies Last Updated], [Candidate Profile Query].[Rank Level], [Candidate Profile Query].[IDP Path], [Candidate Profile Query].[Resume Path], [Candidate Profile Query].[Job Title], [Candidate Profile Query].[Full Supervisor Name], [Candidate Profile Query].[EE ID], [Candidate Profile Query].DOH, [Candidate Profile Query].Grade, [Position Comp Scores].[Min # Years Related Experience Required], [Position Comp Scores].[Min Education Required], [Position Comp Scores].[Competency Requirement 1], [Position Comp Scores].[Competency Requirement 2], [Position Comp Scores].[Competency Requirement 3], [Position Comp Scores].[Competency Requirement 4], [Position Comp Scores].[Competency Requirement 5]
FROM [Position Comp Scores], [Employee Competencies], [Candidate Profile Query]
WHERE ((([Candidate Profile Query].[# Years Related Work Experience])>=[Position Profiles]![Min # Years Related Experience Required]) AND (([Candidate Profile Query].[Education Level])>=[Position Profiles]![Min Education Required]) AND (([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 1] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 2] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 3] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 4] Or ([Employee Competencies].Competency)=[Position Comp Scores]![Competency Requirement 5]) AND (([Position Comp Scores].[Position ID])=[Please Enter Position ID#:]));
KellyJo