Help needed with SQL

Question

I have a table called KWTickets which lists keywords associated with tickets. In theory, there can be any number of keywords associated with a ticket but in practice there is always 1, 2 or 3. However the resulting SQL should not break if there does happen to be more than 3 keywords for a ticket. If this happens, I'm satisfied with a solution that just ignores any keywords over 3 and it does not matter which ones get ignored.

Input table columns: TicketId, KWDesc

Output table columns: TicketId, KWDesc1, KWDesc2, KWDesc3

Answer 1

I solved this by writing a procedure: Here it is if anyone is interested. It's slow but works.

'-------------------------------------------------------------------------------
Public Sub PopTicketKW()
'-------------------------------------------------------------------------------
'  Populate Ticket Keyword table
'  Reads in all keywords for a ticket and populates columns KW1, KW2, KW3 etc.
'  Only reads first three keywords for a ticket
'-------------------------------------------------------------------------------
   On Error GoTo ER
   Dim strSQL As String, lngTkt As Long, lngLastTkt As String, strKW As String
   Dim intSeq As Integer
   Dim rs1 As New ADODB.Recordset
   Dim rs2 As New ADODB.Recordset
   lngLastTkt = 0
   intSeq = 0
   ' Clear out target table
   CurrentProject.Connection.Execute "DELETE * FROM tblTicketKW", , adExecuteNoRecords
   ' Process all Ticket/Keyword data
   strSQL = "SELECT TicketId, KWDesc FROM qryKWTickets ORDER BY TicketId"
   rs1.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
   strSQL = "tblTicketKW"
   rs2.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   ' Write records
   With rs1
   Do While Not .EOF
      lngTkt = Nz(.Fields("TicketId").Value, "")
      strKW = Nz(.Fields("KWDesc").Value, "")
      If (lngTkt = lngLastTkt Or lngLastTkt = 0) Then
         If intSeq = 0 Then
            rs2.AddNew
            rs2.Fields("TicketId").Value = lngTkt
         End If
         intSeq = intSeq + 1
         ' Ignore Keywords after first three
         If intSeq <= 3 Then
            rs2.Fields("KW" & CStr(intSeq)).Value = strKW
         End If
      Else
         rs2.Update
         rs2.AddNew
         rs2.Fields("TicketId").Value = lngTkt
         rs2.Fields("KW1").Value = strKW
         intSeq = 1
      End If
      lngLastTkt = lngTkt
      .MoveNext
   Loop
   End With
   ' Write final record to rs2
   rs2.Update
EX:
   rs1.Close
   rs2.Close
   Set rs1 = Nothing
   Set rs2 = Nothing
   'MsgBox "Finished", , "PopTicketKW"
   Exit Sub
ER:
   MsgBox Err.Description, , "PopTicketKW"
End Sub

Answer 2

You have many-to-many relationship type between Tickets and Keywords.  If you modelled this correctly by a table which resolves it in to two one-to-many relationship types,

TicketKeyWords
....TicketID  (FK)
....KeywordID  (FK)

with the two columns as the table's composite primary key, you would then be able to insert rows by executing a simple INSERT INTO statement.  You would also not be limited to three keywords per ticket as the number of rows inserted would represent the number of keywords to be associated with the ticket.

A possible interface for inserting the rows would be an unbound dialogue form with a combo box to select the ticket and a multi-select list box to select an arbitrary number of keywords.  Code in the form's module would loop through the ItemsSelected collection of the list box, executing an INSERT INTO statement at each iteration of the loop to insert a row into the table, inserting the value of the combo box into one column, and the value of the current row in the list box into the other.  It could also be done as a single INSERT INTO statement by cumulatively building a value list of the selected KeywordID values as the code loops through the ItemsSelected collection, and then building and executing the SQL statement in the code, restricting the rows selected from the Keywords table by applying the IN operator to the value list.

---

Ken Sheridan, Stafford, England