How can make MS Access Query that read a table column based on conidtion

I have a table in MS Access that has time related data and I need to select only the pressure at the minimum depth and maximum depth for each well in each time.

Please find the MS Access table attached and I am expecting to have the follwing results from the query.

Well   Date       Depth   Pressure
1     1/1/2013     23        2333
1     1/1/2013    666       46784
1     3/22/2014    2         6768
1     3/22/2014  777       54654
2     4/1/2014    11         2333
2     4/1/2014    888       6768
test123.mdb
Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query


SELECT A.Well, A.xdate, A.MinOfdepth AS Depth, B.Pressure
FROM (SELECT Table1.well, Table1.xdate, Min(Table1.depth) AS MinOfdepth
FROM Table1
GROUP BY Table1.well, Table1.xdate
UNION ALL
SELECT Table1.well, Table1.xdate, Max(Table1.depth) AS MinOfdepth
FROM Table1
GROUP BY Table1.well, Table1.xdate
Order By 1,2,3
)  AS A INNER JOIN Table1 AS B ON (A.Well = B.Well) AND (A.xDate = B.xDate) AND (A.MinOfdepth = B.Depth)
ORDER BY A.Well, A.xdate, A.MinOfdepth, B.Pressure;



see query1 on your sample db
test123.mdb
0
 
Dale FyeCommented:
You would start out with an aggregate query:

qryWellDates:
SELECT Well, [Date] as PressDate, Min([Depth]) as MinDepth, Max([Depth]) as MaxDepth
FROM yourTable
GROUP BY Well, [Date]

Then, you would join this to your table to get the pressure for those Well/Date combinations

SELECT qryWellDates.Well, qryWellDates.PressDate, qryWellDates.MinDepth as Depth,
             yourTable.Pressure
FROM qryWellDates
INNER JOIN yourTable
ON qryWellDates.Well = yourTable.Well
AND qryWellDates.PressDate = yourTable.[Date]
AND qryWellDates.MinDepth = yourTable.[Depth]
UNION ALL
SELECT qryWellDates.Well, qryWellDates.PressDate, qryWellDates.MaxDepth as Depth,
             yourTable.Pressure
FROM qryWellDates
INNER JOIN yourTable
ON qryWellDates.Well = yourTable.Well
AND qryWellDates.PressDate = yourTable.[Date]
AND qryWellDates.MaxDepth = yourTable.[Depth]
0
 
Mohammed DallagPetroleum ConsultantAuthor Commented:
very good
0
 
Mohammed DallagPetroleum ConsultantAuthor Commented:
I need to change the output of the MS Query to be like the attached file
4-22-2014-12-26-08-PM.jpg
0
All Courses

From novice to tech pro — start learning today.