Select the first maximum value from a set of data 1

[MattGreer]

I have a table of data, "tbl":

I need help to create a query where I select the maximum value from the column 'col1', but only the first occurrence of that value.

I'm using MS Access 365.

Thank you for your help!



Thanks!!


Matt

 

[MattGreer]

[tt]SELECT tbl.dDate, tbl.ID, Max(tbl.col1) AS MaxOfcol1
FROM tbl
GROUP BY tbl.dDate, tbl.ID
HAVING (((Max(tbl.col1)) In (SELECT DISTINCTROW [col1] FROM [tbl] As Tmp )))
ORDER BY tbl.dDate, tbl.ID, Max(tbl.col1);
[/tt]

Just can't figure out how to select the first, max value from col1.

Thanks!!


Matt

 

[Andrzejek]

Could you show the expected output of the query you are trying to create?

BTW, there is no "first, max value from col1", just MAX value from col1

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Matt,
Are you expecting to return the record with the earliest dDate and the maximum col1? Wouldn't that be the 1/4/2020 record?

This should provide what I think you are asking for but it isn't real clear:

SELECT tbl.ID, tbl.dDate, tbl.col1
FROM tbl
WHERE tbl.ID=(SELECT TOP 1 ID from tbl T1 WHERE t1.col1 = (SELECT Max(t2.col1) from tbl t2) order by ddate);

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

 

[MattGreer]

That's amazing. Thank you Duane! Worked perfectly.

edit: The story behind the story is I'm working on a database that stores production estimates. Currently the estimates are given to me as averages per month. So when I'm compiling production increases or decreases, they will happen on a given day and that value stays constant for ~30 days. So I want to pick that maximum production value on the day of that increase. And as it happens, the production will peak only once, so I need the first day of the increase, at the max value of production in the database. I hope that makes sense!

Thanks!!


Matt

 

[MattGreer]

Duane,

I tried using this with the actual db and I get an error: "At most one record can be returned by this subquery."

Is the fact I'm using your SQL string, modified to reflect a query rather than a table, the issue?

Here's the actual string I'm using:

SELECT
     qry_TotalProduction.WellPadName,
     qry_TotalProduction.ProductionDate,
     qry_TotalProduction.SumOfRiskedOil
FROM
     qry_TotalProduction
WHERE
     (((qry_TotalProduction.WellPadName)=
     (SELECT TOP 1 WellPadName FROM qry_TotalProduction t1
          WHERE
               t1.SumOfRiskedOil = (SELECT MAX(t2.SumOfRiskedOil)
          FROM
               qry_TotalProduction t2) ORDER BY ProductionDate)));

Thanks again for your help!

edit: I turned the source query into a "MakeTable" query and adjusted the above to reflect the table, rather than the query, and I got the same warning with no results. Just the warning.
edit2: Perhaps this has to do with the fact that I didn't have a Primary Key in the 'MakeTable' query?
edit3: Yes, I am confused and I don't have a clue here...

Thanks!!


Matt

 

[MattGreer]

OK, so I did a bit of searching and I'm getting a little bit better, I think. Sorry for all the posts and edits.

I have this, which will return one record with the max value:

SELECT TOP 1 Max(qry_TotalProduction.SumOfRiskedOil) AS [Max Oil]
FROM qry_TotalProduction;

I just can't seem to get the ProductionDate to jive with the first time that maximum Oil Production number shows up.



Thanks!!


Matt

 

[dhookom]

Your first data had the ID as unique. Is your WellPadName unique?

I think you need to try give us some actual object names and data records.

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

 

[MattGreer]

Yeah, you're right. Sorry. I was trying not to overcomplicate the subject.

What I'm given is monthly projected production data. I have to use daily production numbers for equipment design and rating, so I take the month total and average it over days. (I'm working on getting something more realistic but that's what I have for now). Whenever a new well is brought online, it adds to the total flow going down a pipeline so I use the database to figure out, on a given day, what the pipeline needs to handle. In the end, I'll change the production dates to match when the well will be brought online and total up the resulting flows. Hope that makes sense.

So I need to focus on the peak flows.

Each WellPad will contain multiple Wells. I need the total flow from each WellPad. So while I have individual production numbers from each Well, I need to add them together to get a total flow from the WellPad. (The reasons being based on geography as the wells will feed into one place on the pipeline.)

Relationships

Table Descriptions

Sample Production Data

Query Results
The query used to get the total production per well pad is this:

SELECT tblWellPads.WellPadName, tblProduction.ProductionDate, Sum(tblProduction.RiskedOil) AS SumOfRiskedOil, Sum(tblProduction.RiskedGas) AS SumOfRiskedGas, Sum(tblProduction.RiskedWater) AS SumOfRiskedWater
FROM (tblWellPads INNER JOIN tblWells ON tblWellPads.PadID = tblWells.[WellPadID]) INNER JOIN tblProduction ON tblWells.WellID = tblProduction.WellID
GROUP BY tblWellPads.WellPadName, tblProduction.ProductionDate
ORDER BY tblProduction.ProductionDate;

This query basically takes all the Wells coming from a Well Pad and gives me the total flow on that particular ProductionDate. The totals will change, obviously, as we manipulate the date that we bring a well online.

Right now I'm just focusing on getting the oil totals out as whatever I do there I'll apply to the Water and Gas.

End Result: So the end result is getting the peak/max flow from each well pad and determining (outside of Access) how that will affect the hydraulics of the pipeline. The database is used to adjust the ProductionDate, which in turn affects when the max flow from each WellPad is observed. I'm doing this in Access because my computer is choking on the large spreadsheets I'm having to deal with, and it seems to make a lot more sense to use Access for this anyway. I was able to do this with PivotTables pretty easily. If there was a way to translate a PivotTable result into an Access query that would solve all my problems!


Thanks for sticking with me through this!





Thanks!!


Matt

 

[dhookom]

Your sample data didn't have any wellpad information. tblWellPads should really have the FacilityID not the facility name.

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

 

[MattGreer]

Again, thanks for keeping up with me.

I need to come up with a sample set of data so you can have access (no pun intended lol) to everything. I really do appreciate your help with all of this!

Thanks!!


Matt