Master Work
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
very good
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]