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