[X]My Assistant

[airplayne]

I work for an oil company and want to get an average of the last 5 tests done for a list of wells. I realize I may need to do this in steps (multiple queries) but need direction on getting started. I have the list of wells, the date of the tests, and the other pertinent data. How do I grab the last 5 well tests for each well? When I try using the Max 5 or Last 5 function on the Test Date field I only get a total of 5 records. I need 5 records for each well.

[cheekybuddha]

Start here on Allen Brown'e site

If you need further assistance, come back with more details of your table/field names structure etc.

hth,

d

[airplayne]

I am trying to follow the examples, but am not understanding the purpose of using the "Dupe" variable in the subqueries.

In the past I have used the MAX function to find the last record for each well based on date, but that doesn't seem to be working for this as I am wanting the last 5 records for each well.

[GroverParkGeorge]

Perhaps some sample data representing what you are working with, and the SQL you wrote, might be useful to someone trying to offer suggestions.

Thanks.

[airplayne]

Here is what I have so far for the subquery:

CODE

SELECT TOP 5 API14
FROM tblWellTests AS Dupe
WHERE Dupe.API14 = tblWellTests.API14
ORDER BY Dupe.TestDate DESC , Dupe.API14;

When I run this a dialogue box pops up asking for the API14.

[cheekybuddha]

Like I said, please describe your tables:

TableName:
Field1 Primary key Autonumber
Field2 Long Integer
Field3 DateTime
etc

Then post the full SQL you have tried

[NimishParikh]

If you need for each well, then you have to include GROUP BY on WellName field.

Nimish

[airplayne]

Ok, after some additional research here is what I came up with:

CODE

SELECT tblWellTests.API14, tblWellTests.TestDate, tblWellTests.TotalFluidRate, tblWellTests.GasRate, tblWellTests.OilRate, tblWellTests.WaterRate
FROM tblWellTests
WHERE (((tblWellTests.TestDate) In (SELECT TOP 5 TestDate
FROM tblWellTests AS Dupe
WHERE Dupe.API14 = tblWellTests.API14
ORDER BY Dupe.TestDate DESC , Dupe.API14;)));

This is returning exactly the records I need. Now I need to get the average of the 5 record groupings. Is this possible within the same query, or should I do this in another one?

[cheekybuddha]

You should be able to wrap the same query:

CODE

SELECT
  t.API14,
  AVG(t.TotalFluidRate) AS TotalFluidRateAvg,
  AVG(t.GasRate) AS GasRateAvg,
  AVG(t.OilRate) AS OilRateAvg,
  AVG(t.WaterRate) AS WaterRateAvg
FROM (
  SELECT wt.API14, wt.TestDate, wt.TotalFluidRate, wt.GasRate, wt.OilRate, wt.WaterRate
  FROM tblWellTests wt
  WHERE wt.TestDate In (
    SELECT TOP 5 TestDate
    FROM tblWellTests AS Dupe
    WHERE Dupe.API14 = wt.API14
    ORDER BY Dupe.TestDate DESC, Dupe.API14
  )
) t
GROUP BY
  t.API14
ORDER BY
  t.API14
;

hth,

d

[airplayne]

I decided to pass the results through another query where I took the API14, and the rate fields which I averaged. This is working well.

Thanks for all your help.