Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Searching For Text Against One-To-Many Relationship

    I have a database that is used to track claims for a company. Normally, I would have put the claim as a single memo field against the warranty claim number:

    tblClaim
    fldClaimNumber
    fldClaimDescription
    (other fields)

    So that if I wanted to do a search for a word in the description I could just do (simplified):
    Code:
    SELECT tblClaim.fldClaimNumber, tblClaim.fldClaimDescription, (other fields)
    FROM tblClaim
    WHERE tblClaim.fldClaimDescription LIKE '*keyword*'
    ORDER BY tblClaim.fldClaimNumber;
    In this particular case however, the data coming from upstream is based on very old software which can only hold up to 32-character text fields (drives me nuts, but can't do anything about it). So for the people upstream entering in the data for a description they put multiple lines into a seperate table like:

    tblClaim
    fldClaimNumber
    (other fields)

    tblDescriptions
    fldClaimNumber
    fldClaimDescriptionSequence
    fldClaimDescription

    So that you actually have multiple lines for a single claim's description. My question is this; how do I write an SQL statement analogous to above but searching against multiple lines? I've tried:
    Code:
    SELECT tblClaim.fldClaimNumber, tblDescriptions.fldClaimDescription, (other fields)
    FROM tblClaim LEFT JOIN tblDescriptions ON (tblClaim.fldClaimNumber = tblDescriptions.fldClaimNumber)
    WHERE tblClaim.fldClaimDescription LIKE '*keyword*'
    ORDER BY tblClaim.fldClaimNumber;
    But this returns multiple rows for a single row, which I don't want. I think I'm missing something easy here but I just can't think anymore. Thanks for any help!
    Me.Geek = True

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use a function to concatenate the different rows of a reclamation in a single line, then base your query on it:

    Code:
    Function OneLineDescription(ClaimID As Long)
    
        Dim strDescription As String
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT fldClaimDescription FROM tblDescriptions " & _
            "WHERE fldClaimNumber = " & ClaimID & _
            " ORDER BY fldClaimDescriptionSequence"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Not IsNull(!fldClaimDescription) Then
                    strDescription = strDescription & " " & !fldClaimDescription
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        OneLineDescription = strDescription
        
    End Function
    Code:
    SELECT tblClaim.fldClaimNumber, tblClaim.fldAnotherField, 
    OneLineDescription([fldClaimNumber]) AS Description
    FROM tblClaim
    WHERE ((tblClaim.fldClaimNumber)=<ClaimNumber>);
    In T-SQL it's possible to use the COALESCE function to do that but though COALESCE is a reserved word in Access SQL i could not succeed in using it.

    Code:
    -- Sample Script to create the table and insert rows
    CREATE TABLE #Temp
    (
    [Numbers] varchar(40)
    )
    INSERT INTO #Temp VALUES('One');
    INSERT INTO #Temp VALUES('Two');
    INSERT INTO #Temp VALUES('Three');
    INSERT INTO #Temp VALUES('Four');
    INSERT INTO #Temp VALUES('Five');
    
    -- Query to combine multiple rows into one
    DECLARE @str NVARCHAR(MAX)
    SELECT @str = COALESCE(@str + '', '') + [Numbers]
    FROM #Temp
    Print @str
    see:Combine multiple rows into one row using SQL Server | Sql Server Blog

    Have a nice day!

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Thanks Sinndho, that's actually what I do right now (loosely based on Dev Ashish's method). But it involves making a seperate table once a week (rebuilding about 800k records takes about 12 hrs), and it'd be nice to get rid of the intermediate table and just look at the live table. I couldn't remember if there was something like the coalesce function in Access or not. thanks!
    Me.Geek = True

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.

    Can't you do what you want to using the query? Do you have to rebuild the table every time?

    Strange enough as I said, COALESCE is a reserved keyword in Access SQL but when you try to use it you receive a message saying that the Coalesce function is not defined. Maybe it is in Access 2007 (one can always hope!).

    Have a nice day!

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    I can use that, but it is rather slow to dimension a recordset and loop through it every time and I didn't know if there was a better way. So right now I just loop through every record and store it in a memo field when I compile the intermediate database. Not ideal, but it works I guess. Thanks.
    Me.Geek = True

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dear Nick,

    I made some tests and came up with interesting conclusions.

    Those tests were performed on two tables of a database (originally those tables are on a SQL server but I imported them into Access). The first table: Orders_Data contains orders data and has a primary key: PK_Orders, the second table: OrderLines_Data contains data related to the different items of an order. This second table has a column FK_Orders which is a pointer to Orders_Data and a composite index FK_Orders_OrderLine based on the columns FK_Orders and OrderLine (an order can have from one to twelve order lines). This seconf table also have, among many others, a column: Description that I use for the tests.The relationship between the tables is like this: [Orders_Data].[PK_Orders]-->[OrderLines_Data].[FK_Orders].

    The test loops through the Orders_Data table to retrieve it's primary key (PK_Orders) and use this key to call a function that concatenates the contents of the Description column of the OrderLines table for every order line related to an order (does it begin to sound familiar?).

    I use three different methods to retrieve the data: an ADODB recordset with the Filter method, a DAO recordset (snapshot) with the FindFirst, FindNext methods and a DAO recordset (table) with the composite index and the Seek method. The recordsets are declared as Public and are open only once by the calling (outer) functions (see code for more details).

    The Orders_Data table has a little more than 12000 lines, the OrderLines_Data table has more than 29000 lines.

    Here's what results:
    - Milliseconds when using an ADODB Recordset and a Filter: 361031
    - Milliseconds when using a DAO Recordset and FindFirst: 137984
    - Milliseconds when using a DAO Recordset and Seek: 1500

    So if you don't already use an index and the Seek method, you probably could gain a lot of performance when processing your data by doing so.

    Have a nice day!

    Code:
    Option Compare Database
    Option Explicit
    Public Indentation As Integer
    Public Declare Function GetTickCount Lib "kernel32" () As Long
    Public ADOrstOrderLines As ADODB.Recordset
    Public DAOrstOrderLines As DAO.Recordset
    
    Function TestTryFilter()
        
        Dim rst As DAO.Recordset
        Dim lngTStart As Long
        
        lngTStart = GetTickCount
        Set ADOrstOrderLines = New ADODB.Recordset
        With ADOrstOrderLines
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        ADOrstOrderLines.Open "OrderLines_Data", CurrentProject.Connection, adOpenStatic, , adCmdTable
        Set rst = CurrentDb.OpenRecordset("Orders_Data", dbOpenTable)
        With rst
            Do Until .EOF
                Debug.Print !PK_Orders, TryFilter(!PK_Orders)
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        ADOrstOrderLines.Close
        Set ADOrstOrderLines = Nothing
        LogEvent String(Indentation, vbTab) & " - Milliseconds when using an ADODB Recordset and a Filter: " & GetTickCount - lngTStart
    
    End Function
    
    Function TestTryFind()
    
        Dim rst As DAO.Recordset
        Dim lngTStart As Long
        
        lngTStart = GetTickCount
        Set DAOrstOrderLines = CurrentDb.OpenRecordset("OrderLines_Data", dbOpenSnapshot)
        Set rst = CurrentDb.OpenRecordset("Orders_Data", dbOpenSnapshot)
        With rst
            Do Until .EOF
                Debug.Print !PK_Orders, TryFind(!PK_Orders)
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        DAOrstOrderLines.Close
        Set DAOrstOrderLines = Nothing
        LogEvent String(Indentation, vbTab) & " - Milliseconds when using a DAO Recordset and FindFirst: " & GetTickCount - lngTStart
    
    End Function
    
    Function TestTrySeek()
    
        Dim rst As DAO.Recordset
        Dim lngTStart As Long
        
        lngTStart = GetTickCount
        Set DAOrstOrderLines = CurrentDb.OpenRecordset("OrderLines_Data", dbOpenTable)
        DAOrstOrderLines.Index = "FK_Orders_OrderLine"
        Set rst = CurrentDb.OpenRecordset("Orders_Data", dbOpenSnapshot)
        With rst
            Do Until .EOF
                Debug.Print !PK_Orders, TrySeek(!PK_Orders)
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        DAOrstOrderLines.Close
        Set DAOrstOrderLines = Nothing
        LogEvent String(Indentation, vbTab) & " - Milliseconds when using a DAO Recordset and Seek: " & GetTickCount - lngTStart
    
    End Function
    
    Public Function TryFilter(OrderID As Long) As String
    
        Dim strDescription As String
        
        With ADOrstOrderLines
            .Filter = "FK_Orders = " & OrderID
            .Sort = "OrderLine ASC"
            Do Until .EOF
                If Not IsNull(!Description) Then
                    strDescription = strDescription & " " & !Description
                End If
                .MoveNext
            Loop
        End With
        TryFilter = strDescription
        
    End Function
    
    Function TryFind(OrderID As Long) As String
    
        Dim strDescription As String
        
        With DAOrstOrderLines
            .FindFirst "FK_Orders = " & OrderID
            Do Until .NoMatch
                If Not IsNull(!Description) Then
                    strDescription = strDescription & " " & !Description
                End If
                .FindNext "FK_Orders = " & OrderID
            Loop
        End With
        TryFind = strDescription
    
    End Function
    
    Function TrySeek(OrderID As Long) As String
    
        Dim strDescription As String
        
        With DAOrstOrderLines
            .Seek "=", OrderID
            If Not .NoMatch Then
                Do
                    If Not IsNull(!Description) Then
                        strDescription = strDescription & " " & !Description
                    End If
                    .MoveNext
                    If .EOF Then Exit Do
                Loop Until !FK_Orders <> OrderID
            End If
        End With
        TrySeek = strDescription
    
    End Function

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Thanks Sinndho! I actually use a fourth method where instead of setting the recordset (the one you call DAOrstOrderLines) to the entire recordset, I use the OpenRecordset(strSQL) to get only the records I need and then loop through them all to build the string. But this gives me something new to try! I'll let you know how it gets a long when it's done. Thanks!
    Me.Geek = True

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Sinndho,

    That seek method is way faster, thanks for the tip! What was taking on the order of a second per record previously is now taking about 0.00013 seconds per record! I can't believe I've never used this method before.

    The only problem was that it looked like it wasn't always finding every description after a .MoveNext. I looked into the .Seek method a little more and the help said this:
    Quote Originally Posted by MS Access Help on DAO.Recordset.Seek
    You must set the current index with the Index property before you use Seek. If the index identifies a nonunique key field, Seek locates the first record that satisfies the criteria.
    So I used Allen Browne's method to create a multi-field index with fldClaimNumber and fldClaimDescriptionSequence, and then assigned it before the seek, something like:
    Code:
    strIndexName = "idxClaimDescriptions"
    
    Dim idx As DAO.Index
    Set idx = tdf.CreateIndex(strIndexName)
    idx.Fields.Append idx.CreateField("fldClaimNumber")
    idx.Fields.Append idx.CreateField("fldClaimDescriptionSequence")
    tdf.Indexes.Append idx
    tdf.Indexes.Refresh
    
    rsDescr.Index = strIndexName
    But then I saw in the help that:
    Quote Originally Posted by MS Access Help on DAO.Recordset.Index
    Records returned from a table-type Recordset object can be ordered only by the indexes defined for the underlying TableDef object.
    Do you know if I can be sure then that by creating this index that it will order the fields by fldClaimNumber and fldClaimDescriptionSequence ascending, such that by using .MoveNext it will find every matching record for the Claim Number?
    Me.Geek = True

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes, according to MS:
    When you sort a table by a multiple-field index, Access sorts first by the first field defined for the index. You set the order of the fields when you create a multiple-field index. If there are records with duplicate values in the first field, Access sorts next by the second field defined for the index, and so on.
    See:http://office.microsoft.com/en-us/ac...103471033.aspx

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •