How do I return query results even if the join result is null in MS Access?
<p>I have 3 tables.  </p>

<p>One contains Profiles as described below:</p>

<pre><code>ID  NM
==============
1   Profile A
2   Profile B
</code></pre>

<p>The second contains assignments:</p>

<pre><code>ID  NM
==============
1   Assignment A
2   Assignment B
</code></pre>

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

<pre><code>ID  P_FID  A_FID  PRIORITY
========================
1   1       2      1
2   1       1      2
</code></pre>

<p>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.</p>

<p>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.</p>

<pre><code>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);
</code></pre>

<p>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?</p>