How do I return query results even if the join result is null in MS Access?

Question

This is a bit difficult to describe, so bare with me on this. I have 3 tables.

One contains Profiles as described below:

ID  NM

==============

1   Profile A

2   Profile B

The second contains assignments:

ID  NM

==============

1   Assignment A

2   Assignment B

My third contains FID's for both and allows you to prioritize them like so:

ID  P_FID  A_FID  PRIORITY

========================

1   1       2      1

2   1       1      2

My problem is populating the third table via a continuous form so the end user has the ability to input priorities. Basically, there is a combo box that lets the user select the appropriate profile. If there are no entries in the third table, it should show you all of the assignments so you can input priorities. If there are already records in that table it should retrieve those values so you can update the priorities.

The following query works great as long as the third table is empty. Once the user inputs priorities and tries to switch to a different profile, it doesn't return any records unless it is the selected profile.

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));

Can this be done in a single query utilizing a union, I would think, or should I just revert to VBA to figure this out? Like I said, it works great as long as the third table is empty or they only work on the first profile they select, beyond that it fails. Does this make sense?

Answer 1

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.

How do I return query results even if the join result is null in MS Access?

Sign up