Solved

How can I create an Access Query that reads a table column based on other column condtions

Posted on 2014-04-30
6
275 Views
Last Modified: 2014-06-25
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 following results from the query.

Well   Date           Pressure_Min_depth          Pressure_Max_depth
1       1/1/2013                  2333                         46784
1       3/22/2014                 6768                        54654
2       4/1/2014                   2333                        6768
test123.mdb
0
Comment
Question by:dallagmm
  • 3
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40031614
Probably a more elegant way of getting this, but define one query as:

SELECT Table1.well, Table1.xdate, Min(Table1.depth) AS MinOfdepth, Max(Table1.depth) AS MaxOfdepth
FROM Table1
GROUP BY Table1.well, Table1.xdate;

Then a second query of:

SELECT qryWellDepths.well, qryWellDepths.xdate, qryWellDepths.MinOfdepth, Table1.pressure, qryWellDepths.MaxOfdepth, Table1_1.pressure
FROM (qryWellDepths INNER JOIN Table1 ON (qryWellDepths.MinOfdepth = Table1.depth) AND (qryWellDepths.well = Table1.well) AND (qryWellDepths.xdate = Table1.xdate)) INNER JOIN Table1 AS Table1_1 ON (qryWellDepths.MaxOfdepth = Table1_1.depth) AND (qryWellDepths.xdate = Table1_1.xdate) AND (qryWellDepths.well = Table1_1.well);

Jim.
0
 
LVL 57
ID: 40031617
Here's the DB by the way..

Jim.
test123.mdb
0
 

Author Comment

by:dallagmm
ID: 40031680
Jim,

I need the pressure at the max depth and min depth not the depth.

Regards,

Dallag
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 57
ID: 40031722
That's what I gave you.  For each well, for each day, the min depth, pressure at that depth, and the max depth and the pressure at that depth.

Jim.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40031896
try this query


Select T.well,T.xdate As [Date], Min(T.Pressure) as Pressure_Min_depth,  Max(T.Pressure) as Pressure_Max_depth
From
(
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
) As T
Group By  T.well,T.xdate
0
 

Author Closing Comment

by:dallagmm
ID: 40156841
Thank you
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dateadd 3 22
DCount using "OR" 4 18
Sql to Replace Folderpath string in MS Access Table field 7 18
Help with SQl and UNION 7 16
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now