[AccessD] Getting last 3 records

On 12 May 2003 at 16:47, Kaup, Chester A wrote:

> I have a table that is full of oil well test data. Basically date and
> volumes. There are dozens of records with different dates for each
> well. I need to get the average of the last (I.e. most recent)  three
> tests for each well. Tried the top values but could not see how to do
> it for each individual well. Your assistance appreciated.
> 
> No trees were killed in the sending of this message. However a large
> number of electrons were terribly inconvenienced. 
> 
> 
Take a look at:

Access Subquery Techniques by Mike Gunderloy

for a good tutorial on this and other subquery techniques (look for 
"top per group"

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dnsmart01/html/sa01j1.asp
(watch for wrap)

Assuming you have a Wells table and a Testdata table:

SELECT WellID, vDate,Volume
FROM Wells INNER JOIN TestData
ON Wells.WellID = TestData.WellID
WHERE vDate IN
  (SELECT TOP 3 vDate
   FROM TestData
   WHERE TestData.WellID = Wells.WellID
   ORDER BY vDate DESC)
ORDER BY WellID ASC, vDate DESC


-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.