SQL Question
How do I return query results even if the join result is null in MS Access?
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?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download