[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
=============================================

  ----- Original Message ----- 
  From: Kaup, Chester 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, May 11, 2006 17:41
  Subject: Re: [AccessD] Top values query problem


  Thanks. That is what I am going to do.

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  Sent: Thursday, May 11, 2006 6:07 AM
  To: accessd at databaseadvisors.com
  Subject: Re: [AccessD] Top values query problem

  Hi Chester

  You could sort on Well and Date in a recordset you open in VBA.
  Then loop through this writing the first six records for each Well to a
  new (temp) table.
  That should run pretty fast.

  /gustav

  >>> Chester_Kaup at kindermorgan.com 10-05-2006 23:28 >>>
  Got around to trying this and had to make 2 changes. PT_Well and
  PT_Status were already strings so I removed the Str function. Query runs
  fine for a few records. The table I am working against has ~500,000
  records. I gave up letting it run it was taking so long. Am I missing
  something or is there a better way to do this such as VBA? My query

  SELECT 
      PT_Well, 
      PT_Date, 
      PT_Status
    FROM 
      dbo_Prod_Tests
    WHERE
      PT_Well & "x" & Str(PT_Date) & "x" & PT_Status IN
        (SELECT TOP 6 
          T.PT_Well & "x" & Str(T.PT_Date) & "x" & T.PT_Status
        FROM 
          dbo_Prod_Tests As T
        WHERE
          dbo_Prod_Tests.PT_Well = T.PT_Well
        ORDER BY 
          T.PT_Well, 
          T.PT_Date DESC;)
    ORDER BY 
      PT_Well, 
      PT_Date DESC;

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com 
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
  Sent: Wednesday, May 10, 2006 4:01 AM
  To: accessd at databaseadvisors.com 
  Subject: Re: [AccessD] Top values query problem

  Hi Chester

  Something like this where you use a pseudo key if you don't have a
  unique key:

    SELECT 
      PT_Well, 
      PT_Date, 
      PT_Status
    FROM 
      dbo_Prod_Tests
    WHERE
      Str(PT_Well) & "x" & Str(PT_Date) & "x" & Str(PT_Status) IN
        (SELECT TOP 6 
          Str(T.PT_Well) & "x" & Str(T.PT_Date) & "x" & Str(T.PT_Status)
        FROM 
          dbo_Prod_Tests As T
        WHERE
          dbo_Prod_Tests.PT_Well = T.PT_Well
        ORDER BY 
          T.PT_Well, 
          T.PT_Date DESC;)
    ORDER BY 
      PT_Well, 
      PT_Date DESC;

  This assumes you have only one record for each Well/Date.

  /gustav

  >>> Chester_Kaup at kindermorgan.com 09-05-2006 23:34 >>>
  When I run the following query I get the top 6 values for only the first
  PT_Well which makes sense the way the query grid makes the query. How do
  I change the query to get the top 6 for each PT_Well? Thanks.

  SELECT TOP 6 PT_Well, PT_Date, PT_Status

  FROM dbo_Prod_Tests

  GROUP BY PT_Well, PT_Date, PT_Status

  ORDER BY PT_Well, PT_Date DESC;