This is a bit difficult to describe, so bare with me on this. I have 3 tables.
One contains Profiles as described below:
1 Profile A
2 Profile B
1 Assignment A
2 Assignment B
ID P_FID A_FID PRIORITY
1 1 2 1
2 1 1 2
SELECT tblProfileForAssignments.PROFILE_FID, tblAssignments.NM, tblProfileForAssignments.PRIORITY
FROM tblAssignments LEFT JOIN tblProfileForAssignments ON tblAssignments.ID = tblProfileForAssignments.ASSGNMNT_FID
WHERE (((tblProfileForAssignments.PROFILE_FID)=[Forms]![frmProfileAssignments]![cmboProfile] Or (tblProfileForAssignments.PROFILE_FID) Is Null));
Turning it into a subquery might give you what you need:
SELECT PRIORITIES.PROFILE_FID, tblAssignments.NM, PRIORITIES.PRIORITY FROM tblAssignments LEFT JOIN (SELECT ASSGNMNT_FID, PROFILE_FID, PRIORITY FROM tblProfileForAssignments WHERE PROFILE_FID = [Forms]![frmProfileAssignments]![cmboProfile]) PRIORITIES ON tblAssignments.ID = PRIORITIES.ASSGNMNT_FID
This should return all assignment names along with any assignments for the specified profile. The query in your example would not display records if assignments for any profile existed and the current profile had no assignments made.