you asked for it:
SQL:
SELECT qrySuccession_Positions.Function, qrySuccession_Positions.[Country key], qrySuccession_Positions.Region, qrySuccession_Positions.[SAP System ID], qrySuccession_Positions.Employee, qrySuccession_Positions.[Last Name], qrySuccession_Positions.[Name prefix], qrySuccession_Positions.[First name], qrySuccession_Positions.Gender, qrySuccession_Positions.Position, qrySuccession_Positions.[Job Skillpool Group], qrySuccession_Positions.[Skillpool Job], qrySuccession_Positions.Job, qrySuccession_Positions.[Job Group], qrySuccession_People.[SG Range2], qrySuccession_Positions.[Availability Start Date], qrySuccession_People.[Sustained Performance], IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="4","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="B","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="1","OK")))))))))))) AS [JG/SG Match], qrySuccession_People.Function, qrySuccession_People.Employee, qrySuccession_People.[Last Name], qrySuccession_People.[Name prefix], qrySuccession_People.[First name], qrySuccession_People.[CEP Current], qrySuccession_People.Nationality, qrySuccession_People.[Line of Business Code], qrySuccession_People.Position, qrySuccession_People.[Date to Position], qrySuccession_People.[Skillpool Group - Primary], qrySuccession_People.[Skill Pool - Primary], qrySuccession_People.[Job Skillpool Group], qrySuccession_People.[Skillpool Job], qrySuccession_People.Job, qrySuccession_People.[Job Group], qrySuccession_People.[Employee Group], qrySuccession_People.[Availability Start Date], qrySuccession_People.Mobility, qrySuccession_People.[Country key], qrySuccession_People.Gender, qrySuccession_People.[CEP Current], qrySuccession_People.[Date of Birth], qrySuccession_People.[Current SG], qrySuccession_People.[SG +1], IIf([qrySuccession_People].[CEP Current]="CEP of JG SE",1.5,IIf([qrySuccession_People].[CEP Current]="CEP of JG LC",1.2,IIf([qrySuccession_People].[CEP Current]="CEP of JG 2-1",0.8,IIf([qrySuccession_People].[CEP Current]="CEP of JG 5-3",0.5,0)))) AS Potential, [Potential]+[Sustained Performance] AS Priority
FROM qrySuccession_Positions, qrySuccession_People
GROUP BY qrySuccession_Positions.Function, qrySuccession_Positions.[Country key], qrySuccession_Positions.Region, qrySuccession_Positions.[SAP System ID], qrySuccession_Positions.Employee, qrySuccession_Positions.[Last Name], qrySuccession_Positions.[Name prefix], qrySuccession_Positions.[First name], qrySuccession_Positions.Gender, qrySuccession_Positions.Position, qrySuccession_Positions.[Job Skillpool Group], qrySuccession_Positions.[Skillpool Job], qrySuccession_Positions.Job, qrySuccession_Positions.[Job Group], qrySuccession_People.[SG Range2], qrySuccession_Positions.[Availability Start Date], qrySuccession_People.[Sustained Performance], IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="2" And [SG Range2]="4","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="1" And [SG Range2]="3","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="1","OK",IIf([qrySuccession_Positions].[Job Group]="A" And [SG Range2]="2","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="B","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="A","OK",IIf([qrySuccession_Positions].[Job Group]="B" And [SG Range2]="1","OK")))))))))))), qrySuccession_People.Function, qrySuccession_People.Employee, qrySuccession_People.[Last Name], qrySuccession_People.[Name prefix], qrySuccession_People.[First name], qrySuccession_People.[CEP Current], qrySuccession_People.Nationality, qrySuccession_People.[Line of Business Code], qrySuccession_People.Position, qrySuccession_People.[Date to Position], qrySuccession_People.[Skillpool Group - Primary], qrySuccession_People.[Skill Pool - Primary], qrySuccession_People.[Job Skillpool Group], qrySuccession_People.[Skillpool Job], qrySuccession_People.Job, qrySuccession_People.[Job Group], qrySuccession_People.[Employee Group], qrySuccession_People.[Availability Start Date], qrySuccession_People.Mobility, qrySuccession_People.[Country key], qrySuccession_People.Gender, qrySuccession_People.[CEP Current], qrySuccession_People.[Date of Birth], qrySuccession_People.[Current SG], qrySuccession_People.[SG +1]
HAVING (((qrySuccession_Positions.Employee)=[Forms]![frmSuccession_Positions]![Employee]) AND ((qrySuccession_Positions.[Last Name])=[Forms]![frmSuccession_Positions]![Last Name]) AND ((qrySuccession_Positions.[First name])=[Forms]![frmSuccession_Positions]![First Name]) AND ((qrySuccession_People.[Last Name])<>[qrySuccession_Positions].[Last Name]) AND ((qrySuccession_People.[First name])<>[qrySuccession_Positions].[First Name]))
ORDER BY 45 DESC;
Dlookup:
Set f = [Forms]![frmSuccession_Positions_TO_People]
varX = DLookup("[Rating]", "tblSuccession_Planning_notes", "Employee ='" & f![qrySuccession_Positions.Employee] _
& "' and [Country Key]='" & f![qrySuccession_Positions.Country key] & "' and [Last Name]='" & f![qrySuccession_Positions.Last Name] _
& "' and [Successor Employee]='" & f![qrySuccession_People.Employee] & "' and [Successor Country Key]='" & f![qrySuccession_People.Country key] _
& "' and [Successor Last Name]='" & f![qrySuccession_People.Last Name] & "'")
Power is Knowledge
Knowledge is Power