Results 1 to 7 of 7

Thread: A Better Way...

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

    Unanswered: A Better Way...

    First off, let me make clear two points:
    (1) My SQL skills are moderate at best
    (2) The structure of the database is fixed by higher powers, so no matter how detestable, no matter how many rules it breaks, I can't change it.
    With that out of the way, let me explain my conundrum...

    I'm working on a database to import Warranty data from an external database; basically we get a .mdb file once a month that has two tables in it with the most recent Warranty data from the last month. I then need to take the data from those two tables and combine them into one master table in a master .mdb file. Here's a sample of how the two tables look...

    Table 1 - "Paid Claims"

    Claim Year | Claim Number | (Many other important fields...)
    ------------------------------------------------------------
    2008 | 1 | ...
    2008 | 2 | ...
    2008 | 3 | ...


    Table 2 - "Claim Problem Descriptions"

    (Number) | (Number) | (Text) | (Number) | (Text)
    Claim Year | Claim Number | Problem Type | Sequence Number | Problem Description
    ------------------------------------------------------------
    2008 | 1 | C | 1 | This is the 1st line of the 2008 - 1 claim.
    2008 | 1 | C | 2 | This is the 2nd line of the 2008 - 1 claim.
    2008 | 1 | F | 1 | This is the 3rd line of the 2008 - 1 claim.
    2008 | 1 | T | 1 | This is the 4th line of the 2008 - 1 claim.
    2008 | 1 | T | 2 | This is the 5th line of the 2008 - 1 claim.
    2008 | 1 | T | 3 | This is the 6th line of the 2008 - 1 claim.
    2008 | 2 | C | 1 | This is the 1st line of the 2008 - 2 claim.
    2008 | 2 | F | 1 | This is the 2nd line of the 2008 - 2 claim.
    2008 | 2 | F | 2 | This is the 3rd line of the 2008 - 2 claim.
    2008 | 2 | T | 1 | This is the 4th line of the 2008 - 2 claim.
    2008 | 3 | C | 1 | This is the 1st line of the 2008 - 3 claim.
    2008 | 3 | T | 1 | This is the 2nd line of the 2008 - 3 claim.
    2008 | 3 | T | 2 | This is the 3rd line of the 2008 - 3 claim.

    So here's where I remind you of point (2); the claims are uniquely identified by BOTH Claim Year and Claim Number (so for example, there is also a claim in the master table with Claim Year = 2007 and Claim Number = 1); you need both fields to uniquely identify a claim. Dumb, I know. But the basic jist is to concatenate the problem descriptions pertaining to the unique claim into a single memo field along with the other fields of the first table. The descriptions need to be in order C, F, then T, and in sequential order. So for example, the Description memo field for 2008-1 claim will look like:
    "This is the 1st line of the 2008 - 1 claim. This is the 2nd line of the 2008 - 1 claim. This is the 3rd line of the 2008 - 1 claim. This is the 4th line of the 2008 - 1 claim. This is the 5th line of the 2008 - 1 claim. This is the 6th line of the 2008 - 1 claim."

    I've developed some code to accomplish this task, but it's all done in VB with recordsets, Dlookups, requires a few temporary tables, and takes about 10 minutes to import about 5000 claims and their descriptions. Here's a snippet of the code with the important bits (after the data has been imported into temp tables):

    Code:
    'Append Paid Claims to Temp Warranty tbl
    Dim strSQL As String
    strSQL = "DELETE tblTempWarranty.* FROM tblTempWarranty;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    strSQL = "INSERT INTO tblTempWarranty " & _
                "SELECT tblTempPaidClaims.* " & _
                "FROM tblTempPaidClaims;"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    'Concatenate and append Problem Description to temp table
    Dim dbs As DAO.Database, _
        rcs As DAO.Recordset, _
        intRecordCount As Long, _
        strDescr As String, _
        strProbType As String, _
        strTemp As String, _
        i As Integer, _
        j As Integer, _
        timStart As Date
    Set dbs = CurrentDb
    Set rcs = dbs.OpenRecordset("tblTempWarranty", dbOpenDynaset)
    intRecordCount = DCount("[ClaimNumber]", "tblTempWarranty")
    rcs.MoveFirst
    timStart = Now
    j = 1
    While Not rcs.EOF  'end of file
        'Update Status
        Me.txtboxStatus = "Phase 2 of 3 - Analyse and Append data" & vbNewLine & _
                            "Concatenating Problem Descriptions" & vbNewLine & _
                            Round(j / intRecordCount * 100) & _
                            "% Complete - " & _
                            Format((Now - timStart) / (j / intRecordCount) - (Now - timStart), "Hh:Nn:Ss") & _
                            " Estimated Time Remaining..."
        DoCmd.RepaintObject acForm, Me.Form.Name
        
        'Initiate variables
        strDescr = ""
        strTemp = ""
        
        'Look for descriptions
        strProbType = "C"
        i = 1
        Do While True
            strTemp = Nz(DLookup("[Problem Description]", _
                    "tblTempClaimProblemDescriptions", _
                    "[Claim Number] = " & rcs!ClaimNumber & " AND " & _
                    "[Claim Year] = " & rcs!ClaimYear & " AND " & _
                    "[Problem Type] = '" & strProbType & "' AND " & _
                    "[Sequence Number] = " & i), "NULL_VALUE")
            If strTemp = "NULL_VALUE" Then
                Exit Do
            Else
                strDescr = strDescr & strTemp & " "
                i = i + 1
            End If
        Loop
        
        strProbType = "F"
        i = 1
        Do While True
            strTemp = Nz(DLookup("[Problem Description]", _
                    "tblTempClaimProblemDescriptions", _
                    "[Claim Number] = " & rcs!ClaimNumber & " AND " & _
                    "[Claim Year] = " & rcs!ClaimYear & " AND " & _
                    "[Problem Type] = '" & strProbType & "' AND " & _
                    "[Sequence Number] = " & i), "NULL_VALUE")
            If strTemp = "NULL_VALUE" Then
                Exit Do
            Else
                strDescr = strDescr & strTemp & " "
                i = i + 1
            End If
        Loop
        
        strProbType = "T"
        i = 1
        Do While True
            strTemp = Nz(DLookup("[Problem Description]", _
                    "tblTempClaimProblemDescriptions", _
                    "[Claim Number] = " & rcs!ClaimNumber & " AND " & _
                    "[Claim Year] = " & rcs!ClaimYear & " AND " & _
                    "[Problem Type] = '" & strProbType & "' AND " & _
                    "[Sequence Number] = " & i), "NULL_VALUE")
            If strTemp = "NULL_VALUE" Then
                Exit Do
            Else
                strDescr = strDescr & strTemp & " "
                i = i + 1
            End If
        Loop
        
        rcs.Edit
        rcs!ProblemDescription = strDescr
        rcs!DateRecordAdded = Now
        rcs.Update
        
        rcs.MoveNext
        j = j + 1
    Wend
    Set rcs = Nothing
    
    'Append temp warranty table
    Me.txtboxStatus = "Phase 2 of 3 - Analyse and Append data" & vbNewLine & _
                        "Appending Temp Warranty Table to Master Warranty Table..."
    DoCmd.RepaintObject acForm, Me.Form.Name
    strSQL = "INSERT INTO tblWarranty " & _
                "SELECT tblTempWarranty.* " & _
                "FROM tblTempWarranty;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    I know there's got to be a better way, probably with SQL. But, like I said at the beginning, my SQL skills aren't that good. If anybody can help me, I would be most grateful. Let me know if I need to ramble on about anything else.
    Last edited by nckdryr; 12-03-08 at 16:36.
    Me.Geek = True

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Well, I'll assume that no one has a better way for me to speed things up, oh well.

    On a related note, when I run this code it takes about 8-10 minutes, so I will usually go do some work on another application or something. But when I go to another applciation, and then try to come back to check on the progress, the form will not update until the code is done executing (thus defeating the point of the Status Text Box). As you can see, I do execute a Repaint on every loop, and I don't mess with the Echo setting on this particular subroutine.

    Anyone out there have any ideas on how to force the form to repaint when I come back to Access?
    Me.Geek = True

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Whack the data into some tables in the db, then write a query to merge the resultset into another temp table (this can be achieved using appropriate JOINs)... This table should have an auto-incrementing field on it, and we should order our resultset on insert to be in order of claim_id, then claim year.

    This should give us a better start point to run set based calculations to produce the final result...


    Now I'm a bit rusty on Access (to say the least) so someone else may have to step in and do the next step... Does Access have partition functions? I.e. row number by group?

    If not, then we have to think a bit harder about the SQL we're going to use - it may have to have a funky subquery, but it should be possible.

    Essentially we're trying to move away from the cursory approach you have got and sort out a couple of one-hitters to obtain the final result.

    Hopefully this will give you, or indeed another contributor, a decent start point to speed things up
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Whack the data
    MY NEW FACEBOOK STATUS!!!


    Quote Originally Posted by georgev
    Essentially we're trying to move away from the cursory approach ...
    yeah, we're trying to actually pay attention now

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe Rudy, I have to say a large number of your posts over the last week have made me giggle out loud!
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks george

    i've been practicing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Assuming that claim year and claim number are unique in table 1 and all claim year claim numbers from table 1 have at least one record in table 2.

    Add an autonumber field to table 1. Create a query using both tables joined by claim year and claim number. Add all fields from both tables except for claim year and claim number - you only need to add them once. Sort the query by claim year, claim number, problem type, and sequence number.

    In VB:
    Set up a variable to hold the new memo field (strMemo)
    Open table 1 as a recordset (rs1)

    Loop through rs1
    Open the query as a recordset (rs2) where the autonumber field is equal to the automber field of the current record in rs1.
    Loop through rs2 setting strMemo = strMemo & " " & rs2!problem
    Write your record to tblWarrenty
    Set strMemo=""
    Move to the next record in rs1

Posting Permissions

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