[AccessD] Top values query problem

I am really curious if the SQL solution I sent you is faster. Did you have
an opportunity to try it? I always tend to use SQL solutions if they are
available over code on the theory they are faster. This may just be
programmer's lore however, so I am curious about this instance.
Jim Hale

-----Original Message-----
From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
Sent: Thursday, May 11, 2006 4:14 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Top values query problem


Against an SQL server table with the wells and the date sorted ascending
461,569 records run time was 23.6094 seconds. Against a native Access
table with the wells sorted ascending and date sorted descending run
time was 11.7656 seconds. The only problem I found was dates did not
sort ascending. I see any reason why. Thanks for the code.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Thursday, May 11, 2006 12:52 PM
To: Access Developers discussion and problem solving
Cc: ADT
Subject: Re: [AccessD] Top values query problem

Chester,

    Sample subroutine given below should get you exceptionally fast
results. T_Result is the target table to which top 6 results for each
value of PT_Well get appended.

    Could you please try it out on your large size data set and confirm
?

A.D.Tejpal
---------------

Code in Click event of command button on Main form
(SF_ResultSub is the name of control on main form
  acting as container for the subform fed by table T_Result)
=============================================
Private Sub CmdResult_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim Qst As String, PtWell As String
    
    Qst = "SELECT DISTINCT PT_Well " & _
            "FROM dbo_Prod_Tests ORDER BY PT_Well;"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(Qst)
    
    ' Clear table T_Result
    Qst = "Delete * From T_Result;"
    db.Execute Qst, dbFailOnError
    
    Do While Not rst.EOF
        ' Append Top 6 records pertaining to current value
        ' of PT_Wells, from main table into T_Result
        PtWell = rst.Fields("PT_Well")
        Qst = "INSERT INTO T_Result " & _
                "(PT_Well, PT_Date, PT_Status) " & _
                "SELECT Q1.PT_Well, Q1.PT_Date,  " & _
                "Q1.PT_Status FROM [SELECT TOP 6 " & _
                "PT_Well, PT_Date, PT_Status " & _
                "FROM dbo_Prod_Tests " & _
                "WHERE PT_Well = '" & PtWell & "' " & _
                "ORDER BY PT_Well, PT_Date DESC]. AS Q1;"
        db.Execute Qst, dbFailOnError
        
        rst.MoveNext
    Loop
    
    Me.SF_ResultSub.Requery
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub
=============================================


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.