SELECT 
tmp.*, 
IIf([CountOfID]=1,"Enabled",IIf([countOfID]=2 And [contract_Type]="Internal","Enabled","Disabled")) AS IsEnabled
FROM tmp 
INNER JOIN 
(SELECT tmp.Consultant_Name, 
tmp.[Period Begin], 
tmp.[Period End], 
tmp.Record_Type, 
Count(tmp.ID) AS CountOfID
FROM tmp
GROUP BY tmp.Consultant_Name, 
tmp.[Period Begin], 
tmp.[Period End], 
tmp.Record_Type
ORDER BY tmp.Consultant_Name, 
tmp.[Period Begin], 
tmp.[Period End], 
tmp.Record_Type) AS qry_IntExtCatch 
ON (tmp.Record_Type = qry_IntExtCatch.Record_Type) 
    AND (tmp.[Period End] = qry_IntExtCatch.[Period End]) 
    AND (tmp.[Period Begin] = qry_IntExtCatch.[Period Begin]) 
    AND (tmp.Consultant_Name = qry_IntExtCatch.Consultant_Name)
ORDER BY tmp.Consultant_Name, 
tmp.[Period Begin], 
tmp.[Period End], 
tmp.Record_Type;

tmp.Consultant_Name, 
tmp.[Period Begin], 
tmp.[Period End], 
tmp.Record_Type