SQL questions 1

[fileman1]

I have a multi select list box filled with subject titles. I am trying to do 2 things but getting nowhere.

If one row of the list box is selected, I want to select all records that are applicable. When more than one row is selected, I am trying to identify records that have both selections being applicable to them, and those records that do not have both then drop out of mt selected records list.

I just seem to continually build my list, and show duplications. Cannot work out how I build in an AND statement when not knowing what/number of items will be selected in the list.

    Dim i As Variant
    Dim Criteria As String, Criteria2 As String, MySql As String

    Criteria = "": Criteria2 = ""

    For Each i In Me![List947].ItemsSelected
        Criteria2 = Criteria2 & "," & Me.List947.Column(0, i)
    Next i
   
    MySql = "SELECT MAIN.ID1, JUNCTION1.KeywordID FROM KEYWORD INNER JOIN "
    MySql = MySql & "(MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1) ON KEYWORD.ID2 = JUNCTION1.ID2"
    MySql = MySql & " WHERE Junction1.KeywordID IN (" & Mid(Criteria2, 2) & ")"
   

    Me.LK.RowSource = MySql

 

[PHV]

A starting point showing no duplication and only MAIN records having ALL selected keywords:

Dim i As Variant, nbItems As Integer
Dim Criteria2 As String, MySql As String
For Each i In Me!List947.ItemsSelected
    Criteria2 = Criteria2 & "," & Me!List947.Column(0, i)
    nbItems = nbItems + 1
Next i
MySql = "SELECT ID1, '" & Mid(Criteria2, 2) & "' AS KeywordIDs FROM JUNCTION1"
MySql = MySql & " WHERE KeywordID IN (" & Mid(Criteria2, 2) & ")"
MySql = MySql & " GROUP BY ID1 HAVING Count(*)=" & nbItems
Me!LK.RowSource = MySql

Assumption: there is an unique index on JUNCTION1(ID1,KeywordID)

BTW, what is the difference between ID2 and KeywordID in this table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thank you PHV, that's got me moving again.
KeywordID in the Junction table is coming out, it was put there in the beginning so I could see things happening. Both ID1 and ID2 in the junction table are related to primary keys of tables either side in relationship.

Appreciated your help again.

 

[fileman1]

I tried adding in other items from my main table ie. Main.Status, thinking ID1 was from the main table, but of course it's the junction table ID1. Can I get reference to the main table (as ID1 is the primary key of the main table, carried through as foreign key in Junction) so I can include other items in the list, i.e Main.Status etc

Otherwise I am happy to use the existing list as link to the relevant records. I really wish I could understand SQL language and be more independent, maybe in next life.

 

[PHV]

Like this ?
MySql = "SELECT M.ID1, M.Status, '" & Mid(Criteria2, 2) & "' AS KeywordIDs"
MySql = MySql & " FROM JUNCTION1 J INNER JOIN Main M ON J.ID1=M.ID1
MySql = MySql & " WHERE J.KeywordID IN (" & Mid(Criteria2, 2) & ")"
MySql = MySql & " GROUP BY M.ID1, M.Status HAVING Count(*)=" & nbItems

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fileman1]

Thanks, that's ideal.

I did try this but it was dead

' MySql = "SELECT JUNCTION1.ID1, '" & Mid(Criteria2, 2) & "' AS KeywordRef, MAIN.IDNumber"
   ' MySql = MySql & " FROM MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1"
   ' MySql = MySql & " WHERE KeywordID IN (" & Mid(Criteria2, 2) & ")"
   ' MySql = MySql & " GROUP BY ID1 HAVING Count(*)=" & nbItems
   ' Me!LK.RowSource = MySql

Thanks again