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

[MattGreer]

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)

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.

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

 

[dhookom]

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

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

 

[MattGreer]

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

 

[Andrzejek]

If your data in qryProductionDates1 query looks something like this:

[pre]
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
[/pre]
And you run your Select:
[pre]
SELECT WellName,
Min(ProductionDate) AS MinOfProductionDate
FROM qryProductionDates1
GROUP BY WellName; [/pre]

You should be getting something like this:

[pre]
WellName MinOfProductionDate
Bob 1/1/2010
Sue 1/2/2012
Bill 2/2/2010[/pre]

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.

 

[dhookom]

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

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

 

[MattGreer]

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

 

[dhookom]

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

 

[MattGreer]

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

 

[Andrzejek]

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 , 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.


---- Andy

There is a great need for a sarcasm font.

 

[MattGreer]

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