none
Help needed with SQL RRS feed

  • 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


    Monday, July 3, 2017 11:34 PM

Answers

  • 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


    Friday, July 7, 2017 8:37 PM

All replies

  • 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


    Friday, July 7, 2017 8:37 PM
  • 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


    • Edited by Ken Sheridan Saturday, July 8, 2017 5:31 PM Punctuation.
    Saturday, July 8, 2017 5:28 PM