×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MS Access 365 - Return The First, Earliest by Date Record
2

MS Access 365 - Return The First, Earliest by Date Record

MS Access 365 - Return The First, Earliest by Date Record

(OP)
I have a database with production estimates from oil wells. The wells come online on a certain date. Production estimates are entered in the database by day. I need to adjust all the [ProductionDates] for a given [WellName] by a certain number of days to create what-if scenarios and so forth. But in order to know how many days to adjust, I need to know what the first date is for each well.

This query (named ProductionDates1)

CODE --> SQL

SELECT tblWells.WellName, tblProduction.ProductionDate
FROM tblWells INNER JOIN tblProduction ON tblWells.WellID = tblProduction.WellID
ORDER BY tblWells.WellName, tblProduction.ProductionDate; 

gives me each Well, and every day of production in the database.

How to I return just the first, earliest date for each Well?

If I run this, I get the date for that first well repeated for every well.

CODE --> SQL

SELECT qryProductionDates1.WellName, Min(qryProductionDates1.ProductionDate) AS MinOfProductionDate
FROM qryProductionDates1
GROUP BY qryProductionDates1.WellName; 

Here's a reference to the Relationships (I did fix the FacilityID/FacilityName)

Thanks!!


Matt

RE: MS Access 365 - Return The First, Earliest by Date Record

Is this the same question/thread you didn't reply to me?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS Access 365 - Return The First, Earliest by Date Record

(OP)
Since that point I've adjusted quite a bit how I'm retrieving other information from the database. This isn't a question of the maximum value in a set of data; it's trying to obtain the very first entry (based on a date) in the database given the value of a 2nd field. Given my level of expertise it doesn't seem like the same question, but for an expert like yourself this might be the same thing. I don't know?

Sorry for not replying yet; I haven't been able to return to that other thread. Because of deadlines I have to use the manual method I was using before. Deadline is today so I should be able to return to that other thread.

Thanks!!


Matt

RE: MS Access 365 - Return The First, Earliest by Date Record

If your data in qryProductionDates1 query looks something like this:

WellName    ProductionDate   ....
Bob         1/1/2010         ....
Bob         2/2/2012
Bob         3/3/2013
Sue         1/2/2012
Sue         3/4/2015
Sue         4/5/2019
Bill        2/2/2010
Bill        3/3/2013
Bill        4/4/2018
 
And you run your Select:
SELECT WellName, 
Min(ProductionDate) AS MinOfProductionDate
FROM qryProductionDates1
GROUP BY WellName;  

You should be getting something like this:

WellName    MinOfProductionDate
Bob         1/1/2010
Sue         1/2/2012
Bill        2/2/2010 

If not, could you share the sample of data (jus the two fields should be enough) from your qryProductionDates1 query?


---- Andy

There is a great need for a sarcasm font.

RE: MS Access 365 - Return The First, Earliest by Date Record

Are you expecting to get the details as well as the minimum date in each row?

CODE --> sql

SELECT tblWells.WellName, tblProduction.ProductionDate, 
(SELECT Min(ProductionDate) FROM tblProduction P WHERE P.WellID = tblProduction.WellID) As FirstProdDate
FROM tblWells INNER JOIN tblProduction ON tblWells.WellID = tblProduction.WellID
ORDER BY tblWells.WellName, tblProduction.ProductionDate; 


You really should be providing some actual sample records and desired output. This would be so much clearer.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS Access 365 - Return The First, Earliest by Date Record

(OP)
Would it be best to have downloadable files? Like spreadsheet and the actual database? I can do that, I just have to anonymize the data and details of course. Is that allowable?

Thanks!!


Matt

RE: MS Access 365 - Return The First, Earliest by Date Record

An Access, csv, or Excel file would be nice and make it much easier and less time consuming for those who would like to assist.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: MS Access 365 - Return The First, Earliest by Date Record

(OP)
Hey guys, I'm really sorry, but it looks like the dates in the database are screwed up, by me of course, somehow in my attempts to achieve my goal in different ways. I will follow up later tonight on this. I really do need you guys' help. Thank you for your time. Both your queries probably work as desired but the source data has been normalized so I see. I'll have to re-import the data. I'll get that done and report back.

The whole point of this is to create what-if scenarios to determine if our facilities can handle the production volumes. If we bring more production on sooner, the volume goes up and the equipment might not be able to handle those flows. The database holds the production forecasts and Excel will do the interpretation, graphs, etc.

So for this particular exercise, what I'm attempting to do is this:

Get the first production date for each well from the database (call this IP, or Initial Production date). Compare the database IP to a "what-if IP" date entered in the front-end workbook, and then modify all the database production dates by that difference. So if Well 1 has an IP date of 11/1/2019, and we wanted to see what would happen if we brought the well online later, say, 2/1/2020, wall the production dates in the database would need to be pushed forward by three months.

I'll report back. Thank you both for your time and consideration. I wouldn't be able to get this done without folks like you all! :)

Thanks!!


Matt

RE: MS Access 365 - Return The First, Earliest by Date Record

You know, all of the ‘business rules’ that you presented are nice and dandy, but honestly, we (well, I…) don’t really care. The data could be about oil wells, gold pieces, UFO reports worm, or secret FBI… (whatever they do). But you would get a lot better and faster help if you would just state: This is the sample data that I have to deal with, I did try this and that, but what I really need to get as an outcome is this. That’s what I (and Duane?) are trying to say. smile


---- Andy

There is a great need for a sarcasm font.

RE: MS Access 365 - Return The First, Earliest by Date Record

(OP)
Understood Andy. I just want to be careful. I really enjoy my job and the fact that I get to do cool stuff like this that will, in the end, be a HUGE benefit to the company and not just myself!

Deadlines being what they are, I had to drop this activity and use the existing tool I developed (100% Excel) and deal with the incredible slowness due to the aforementioned amount of data. Suffice to say once I get through budgeting and project approval I'll pick this back up. In the meantime hopefully I'll be able to generate some sample data and work from there. I really do appreciate you guys, and all the folks here at Tek Tips, that are so generous and giving with their time and knowledge. A star is not enough! :)

So I'll be back. Hopefully the thread won't be closed but I can just link to it if need be.

Thanks!!


Matt

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close