CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
user3654610
user3654610

Reputation: 23

Left Join not working with WHERE condition set to <>

Before I ask this question I must admit that I am new to SQL but here it goes:

I have 3 tables: Crown Facility, tblSubProjects, and lnkSubProjectFacility.

I use lnkSubProjectFacility to assign facilities to sub-projects and vice-verse. When the user selects a sub-project from a combo box (cboAddFacSubProject) in a form, the AfterUpdate event in the combo box causes the 2 listboxes to populate with the associated records in the lnkSubProjectFacility table records.

The LEFT listbox (lstAddSubProjectFacilities) displays all facilities associated with the sub-project and the RIGHT (lstAddSubProFac) displays all available remaining facilities that the user can assign, with a command button, and remove a available facility from the right listbox and moving it to the left (thus creating a new record in the lnkSubProjectFacility table).

Everything works fine except I cannot seem to get the right listbox to populate with the correct available facilities in the combo box's AfterUpdate event with the VBA code below:

Private Sub cboAddFacSubProject_AfterUpdate()
Dim strSQL As String, strSQL2 As String

'String SQL statment variable for the LEFT listbox - to display all ASSIGNED facilities
strSQL = "SELECT [CROWN Facility].FACILITY_ID, " & _
        "[CROWN Facility].FACILITY_NAME, " & _
        "lnkSubProjectFacility.SUBPROJECT_ID " & _
        "FROM [CROWN Facility] INNER JOIN lnkSubProjectFacility " & _
        "ON [CROWN Facility].FACILITY_ID = lnkSubProjectFacility.FACILITY_ID " & _
        "WHERE lnkSubProjectFacility.SUBPROJECT_ID =" & Me.cboAddFacSubProject & " " & _
        "ORDER BY [CROWN Facility].FACILITY_NAME"

'String SQL statment variable for the RIGHT listbox - to display all AVAILABLE facilities
strSQL2 = "SELECT [CROWN Facility].FACILITY_ID, " & _
        "[CROWN Facility].FACILITY_NAME, " & _
        "lnkSubProjectFacility.SUBPROJECT_ID " & _
        "FROM [CROWN Facility] LEFT JOIN lnkSubProjectFacility " & _
        "ON [CROWN Facility].FACILITY_ID = lnkSubProjectFacility.FACILITY_ID " & _
        "WHERE lnkSubProjectFacility.SUBPROJECT_ID <>" & Me.cboAddFacSubProject & " " & _
        "ORDER BY [CROWN Facility].FACILITY_NAME"

'RowSource for the LEFT listbox - to display all assigned facilities
lstAddSubProjectFacilities.RowSource = strSQL

'RowSource for the RIGHT listbox - to display all available facilities
lstAddSubProFac.RowSource = strSQL2

'This is just updating a label showing the count of items in the left listbox
lblListCt.Caption = lstAddSubProjectFacilities.ListCount & " Facilities Selected"

End Sub

After executing this code, everything is fine in the left listbox, with all assigned records for that sub-project showing correctly. However, despite the right listbox excluding all facilities shown in the left, it also excludes ANY facilities that were assigned to any other sub-projects in that table.

In addition for searching for hours for this question, I have experimented with the strSQL2 variable by trying to add IS NULL at the end (which of course does not work) and many other things such as changing the JOIN types. Most recently, I changed WHERE to AND which returns nothing.

I am sure there is a fairly simple solution to this but I would be very appreciative of any assistance to get me there!

Note: I am using Access 2010 but I do not think that makes any difference.

EDIT: Here is the structure for the lnkSubProjectFacility table:

SUBPROJECT_ID    FACILITY_ID
7                20000003   
7                20000025   
7                20000027   
8                20010302   
8                20021781   
9                20040035   
9                20044392   
10               20045465
17               10000282   
17               10000452   
17               10000844   
21               20000005   
21               20000019   
21               20000026

CROWN Facility table structure:

FACILITY_ID FACILITY_NAME
20000003    Barnes
20000025    Bio-Medical Applications
20000027    Barnes Center
20010302    Atlantic
20021781    Anthonys Hospital
20040035    Black Hawk
20044392    Ames
20045465    Arnold
10000282    BETHANY
10000452    ANDOVER
10000844    Ankeny
20000005    Columbia
20000019    Baptist
20000026    Childrens Hospital

tblSubProjects table structure:

SUBPROJECT_ID    SUBPROJECT          
7            Service Project1
7            Service Project1
7            Service Project1
8            Service Project2
8            Service Project2
9            Service Project3
9            Service Project3
10           Service Project4
17           CatheterReduction1
17           CatheterReduction1
17           CatheterReduction1
21           Patient Access3
21           Patient Access3
21           Patient Access3

Upvotes: 1

Views: 117

Answers (1)

czifro
czifro

Reputation: 784

I am going to assume that lnkSubProjectFacility is meant to relate Facility to tblSubProjects. lnkSubProjectFacility seems to be the only table I would change as far as schema

lnkSubProjectFacility
         SPF_Id,  SubProject_Id, Facility_Id
           1          7           20000003
           2          7           20000025
           3          7           20000027
           4          8           20010302
           .          .               .
           .          .               .
           .          .               .

try to normalize the data as much as possible that way it is easier to write queries.

if you are trying to write a query that gets all facilities that have not been assigned a subproject:

  select [CROWN FACILITY].FACILITY_ID, [CROWN FACILITY].FACILITY_NAME
  from [CROWN FACILITY] as CF
  where not exists (select * 
                    from lnkSubProjectFacility as lSPF
                    where CF.FACILITY_ID == lSPF.FACILITY_ID)

i believe that query should work, hard to tell when I cannot test it. sometimes a join is not the answer. a join can look a lot nicer, but sometimes a subquery is better

Upvotes: 1

Related Questions