By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Returning specific data on a query

P: 2
I'm rusty on Access - have not used it since 2000. Currently have Access 2013.

I would like to have the query return the first 3 months of data on oil production based on the completion date of the well.

Expand|Select|Wrap|Line Numbers
  1. Data fields:
  2. Completion Date         Production date:
  3. 1/9/2009                1/1/2009
  4. 1/9/2009                2/1/2009
  5. 1/9/2009                3/1/2009
  6. 1/9/2009                4/1/2009
  7. 1/9/2009                1/1/2010 
Data expample above is for one well. I need the query to look at the completion date and return the first 3 production date data lines. Not all wells have a january completion date a sum type query won't work because wells that start in december would have production in jan-mar.

Hope someone can help...I'm feeling very rusty..
Dec 18 '13 #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,397
that's all of the data?
nothing to indicate the well such as:
Expand|Select|Wrap|Line Numbers
  1. Data fields:
  2. WellExample   Completion Date         Production date:
  3. 1                1/9/2009                1/1/2009
  4. 1                1/9/2009                2/1/2009
  5. 1                1/9/2009                3/1/2009
  6. 2                1/9/2009                4/1/2009
  7. 2                1/9/2009                1/1/2010 
Dec 18 '13 #2

NeoPa
Expert Mod 15k+
P: 31,494
To help you, we'd need you to specify the problem more clearly. How is "first three months" defined?
Dec 19 '13 #3

P: 2
Thank you for you help! The first 3 months is defined as the first 3 months after the completion date. So if a well is completed on 1/1/10 - I need the query to return the production data associated with 1/1/10, 2/1/10 and 3/1/10.
Dec 19 '13 #4

zmbd
Expert Mod 5K+
P: 5,397
From the data you've provided, I really don't see any reasonable means to reliably return the results you desire.

The best I can tell you is that you either have not provided enough of your dataset to for us to help and/or your database design has failed you in the most miserable way possible...

So what I'm going to ask you to do is:
1) Go back to your data. Almost anything in addition to what you have here might help.

2) reveiw the following sites for how to design a RDMS.
---
MS Access 2010 Tutorials
Access Basics - By Crystal
Micrsoft: Office2010 tutorials
Microsoft: Getting started with Access 2010
> Database Normalization and Table Structures.
---

3) I am abhorent to provide the following:
Based on the data you posted in #1

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 3 Table3.Completion , Table3.Production 
  2. FROM Table3
  3. WHERE ((([Production]>=[Completion]));
  4.  
This will return the first three records wherein the production date is greater than 1/9/2009 the 2/1/2009 - 4/1/2009 IT will not return the 1/1/2009 because that date is greater than the 1/9/2009
FURTHERMORE: Even if you add 100 more records, it is only going to return 2/1/2009 - 4/1/2009

You see, you've really not given us anything to really work with.
Dec 19 '13 #5

NeoPa
Expert Mod 15k+
P: 31,494
It may be as simple as :
Expand|Select|Wrap|Line Numbers
  1. SELECT [WellID]
  2.      , [Completion]
  3.      , [Production]
  4. FROM   [YourTable]
  5. WHERE  ([Production] Between [Completion] And DateAdd('m',3,[Completion]))
Dec 20 '13 #6

zmbd
Expert Mod 5K+
P: 5,397
yes, but no [WellID] offered either in post 1 nor 4; however, I should have thought of the "Between"
Dec 20 '13 #7

NeoPa
Expert Mod 15k+
P: 31,494
No need to beat yourself up about it Z. Sometimes it occurs to one and other times not. It often depends on how clearly the question is understood at the time.

That, of course, depends on how it's written which, in turn, depends on the efforts of the poster, as well as - and here's the kicker - their experience. Many a well-intentioned member has done their best to make the question clearer, only to have exactly the reverse effect. Simply because they lack the experience to realise what things are most helpful and what things are not so much.

For those of us wih more experience it seems just common sense, but often that's because we're so used to dealing with such situations.

I suspect Domino felt he was simplifying the whole matter by leaving out any reference to the Well info in the explanation.
Dec 20 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.