UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Getting Averages, Access 2016    
 
   
airplayne
post Nov 20 2019, 10:01 AM
Post#1



Posts: 591
Joined: 17-February 09
From: West Texas


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.
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:04 AM
Post#2


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
airplayne
post Nov 20 2019, 11:13 AM
Post#3



Posts: 591
Joined: 17-February 09
From: West Texas


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.
Go to the top of the page
 
GroverParkGeorge
post Nov 20 2019, 11:28 AM
Post#4


UA Admin
Posts: 36,194
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
airplayne
post Nov 20 2019, 11:32 AM
Post#5



Posts: 591
Joined: 17-February 09
From: West Texas


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.
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 11:46 AM
Post#6


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
NimishParikh
post Nov 20 2019, 11:56 AM
Post#7



Posts: 235
Joined: 30-November 10



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

Nimish
Go to the top of the page
 
airplayne
post Nov 20 2019, 12:31 PM
Post#8



Posts: 591
Joined: 17-February 09
From: West Texas


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?
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 12:42 PM
Post#9


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
airplayne
post Nov 29 2019, 03:45 AM
Post#10



Posts: 591
Joined: 17-February 09
From: West Texas


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.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 08:11 AM